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!