Bet you don't know about these SQL Reporting Services perfmon counters

Thanks to Tudor Trufinescu and Matthew Hofacker for getting into a discussion on these suckers!

 

As you know, SQL Reporting Services ships with a slew of perfmon counters. However, there are some additional counters which are undocumented and must be activated before they work.

 

Here is a list of the counters in question:

 

Active Database Connections

Active Datasource Connections

Active Threads

Byte Count

Request Time

Row Count

Time between Finish Calls

Time in compression

Time in data source Access

Time in database

Time in processing

Time in Rendering

Time Waiting on the Next Stream

 

You activate them by using the installutil utility against the ReportingServicesLibrary.dll assembly. First, you unregister, then you re-register it, like this:

 

(Make sure you use the right version of the tool):

 

C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\InstallUtil.exe /U "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\ReportingServicesLibrary.dll"

C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\InstallUtil.exe "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\ReportingServicesLibrary.dll"

 

 

Next, open up perfmon, and check out the RS Web Service object...you'll see all the nice new counters.

 

Here's what they all do:

 

My favorites are the 5 below. They duplicate some of the information which gets dumped in the ReportServer..ExecutionLog table when you run a report (How long a report took to get its data, how long it took to process, how long the report took to render, how many bytes were returned to the client, and the number of rows returned by the report) I like these counters because they keep me from having to run the same query against ExecutionLog over and over again if I need to check perf profiles immediately and don't want to run the Execution Logging DTS package:

 

Time in data source access - The number of milliseconds spent accessing data for reports - this number includes executing the queries and fetching the results.

 

Time in processing - The number of milliseconds spent processing the last report which was executed

           

Time in rendering - Number of milliseconds used to render the last report

 

Byte count - This one shows you the number of bytes that were returned to the client for the last request.

 

Row Count - The Number of rows returned by last report executed.

 

BTW, when you add the first 3 counters in, they default to a scale of 1 -- make sure you set them to .0001 so that the results are displayed correctly...the same goes for most of the time-related counters we're talking about here.

 

Others include:

 

Active Database Connections - This sucker shows you the number of connections to the ReportServer database.

           

Active Datasource Connections - Reflects the number of data source connections being used by reports which are currently running.

 

Active Threads - Depending on whether you look at this counter for the Web Service or Windows Service, you'll see different values. For the web service it shows the # of threads being used to service report requests. If you look at the counter for the Windows service, you'll get number of threads currently active including working threads that have to do with maintenance and polling.

 

Time in database - How many milliseconds were spent in the Report Server catalog getting report-specific catalog information in order to return the last report requested

           

Time in compression - How many ms were spent compressing snapshots or PDF reports

 

Request Time - The total amount of time an HTTP request takes between the time it hits "Reporting Services code" and when it leaves the code's control. If your report has multiple charts and images, this counter could end up returning multiple values very quickly in succession -- one for the main report, and one for each stream (image or chart) inside the report.

 

I haven’t nailed down Time Between Finish Calls and Time Waiting on the Next Stream completely, but they deal with the new client-side printing feature in SP2. When we use client-side printing, we must call the server once for each page we need to render. If you create a test report with, lets say, 800 pages and client-print it, you’ll see the Total Requests RS Web Service tick off 800 times.

 

From what I gather, each time we make one of these 800 requests, our server-side rendering extension renders a page for us as a stream, and then calls Finish(). So, I guess these counters tell us how quickly we’re moving from one page to another and if there’s any latency on the client-side while we wait to get another stream to print.

 

One more (general) hint about using these counters – Make sure the RS Web Service and/or Windows Service has actually started up BEFORE you start adding the perfmon counters. If the “Instance” column for the counter that you add doesn’t read “mssqlserver”, you need to remove it, do something to get the web/windows service running (like maybe launch Report Manager), and then add it again. If you don’t the counter won’t return any information at all.

 

I think using all this new information will help ME tune the performance of my SSRS boxes. hope they help you.