I’ve Got Your Back

Last week,  I showed you some nifty graphs that display Perfmon data from SQL Server. At the time, I warned you that performance was pretty slow and that improvements could likely be had by changing the report to use stored procedures instead of the view I used. I left that as an exercise for the reader.

Well, turn in your homework readers, because this week, I’m giving you the answer to how to do that!

Truth be told, I was starting you use these reports more frequently and the slow load times were driving me crazy. I’m pleased to report that, by switching to stored procedures and tweaking the view a bit, I was able to get an improvement in performance on the order of two magnitudes. Yes, these reports now run 100 times faster!

Here’s how to make the changes:

First, change the view to get rid of the MAX and MIN values. The report doesn’t use them, so there’s no point in having SQL calculate them. Either comment out lines 15 and 16 of the procedure or delete them.

Second, we’re going to make three stored procedures for use by the report. One will be to get a list of distinct machine names, one will get a list of counters for the particular machine selected, and the last will get data from the view while supplying the machine name and counter name as parameters for a WHERE clause.

Here are the procedures:

CREATE PROCEDURE sp_DistinctMachineNames

AS

SELECT  DISTINCT cdt.MachineName
FROM    dbo.CounterDetails AS cdt

GO

CREATE PROCEDURE sp_DistinctCounterDisplayNames
	(@MachineName varchar(1024))

AS

SELECT  DISTINCT CASE
        	WHEN cdt.ObjectName LIKE 'SQLServer:%' THEN cdt.CounterName + ': SQL Default Instance'
        	WHEN cdt.ObjectName LIKE 'MSSQL$%' THEN
        		cdt.CounterName + ': SQL ' + SUBSTRING(cdt.ObjectName,CHARINDEX('$',cdt.ObjectName)+1,CHARINDEX(':',cdt.ObjectName)-CHARINDEX('$',cdt.ObjectName)-1)
        		+ ' Instance'
        	WHEN cdt.CounterName =  '% Usage' THEN 'Page File % Usage'
        	ELSE cdt.CounterName
        END AS CounterDisplayName
FROM    dbo.CounterDetails AS cdt
WHERE	cdt.MachineName = @MachineName

GO

CREATE PROCEDURE sp_PerfmonData
	(	@MachineName varchar(1024),
		@CounterDisplayName varchar (1024))

AS

SELECT	MachineName,
		CounterDisplayName,
		InstanceName,
		avgValue,
		FormatedDateTime
FROM	vw_PerfmonStats
WHERE	MachineName = @MachineName
		AND CounterDisplayName = @CounterDisplayName

GO

Next, grant EXECUTE permissions on these procedures to the login your report uses to connect to the database.

The final step is to modify your report definition to use the new procedures. This is relatively easy – simply change the definitions of the datasets. First, let’s change the ds_Machines dataset. Before, this was a simple query SELECTing DISTINCT machine names from the view. We will change this to use our new stored procedure:

Now, let’s change the ds_Counters dataset. Before, this was SELECT DISTINCT MachineName, CounterDisplayName from the view. Now, it will be a stored procedure called with the MachineName report parameter supplied as an input:

Be sure to clear the Filters tab. Our stored procedure will do this for us now.

Lastly,we need to change the main dataset for the report from the view to our last stored procedure:

Again, be sure to clear the filters tab.

That’s it! Deploy the report to your SSRS and marvel and how much faster it is!

Share

Leave a Reply

Your email address will not be published. Required fields are marked *

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.