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.

Comments (16)

  1. Anonymous says:

    Altova Releases XML Engines To Developers

    An ASP.NET Custom Control for Dynamically Filtering Data…

  2. Anonymous says:

    Secret SQL Reporting Services Perfmon counters uncovered!

    Scott C. Reynolds has a great comeback…

  3. Anonymous says:

    What service pack is required for these hidden counters? We have installations without any service packs and am unable to see them.

    Unfortunately, it takes some effort to get the SPs approved and pushed out to the servers.

    Thanks!

  4. For 2005, you don’t need a SP…for 2000, I’m honestly not sure as I never tested each version to find out…I know that the box I originally played around with this stuff on was at least SSRS SP1, however.

  5. Anonymous says:

    Hi! Followed your example, for SQL 2005, WinXPPro to add the RS Web Service counters. They show up in my list but the "Add" button is greyed out. I’m Admin to my machine, so not sure why I can’t add them. Any suggestions? Thanks!

  6. I’ve never seen this. Are you able to add the other SSRS counters?

  7. Anonymous says:

    I have tried the same in Sql 2005. Options have been added in the list but add button turns grey. Any idea how to fix this?

  8. Anonymous says:

    I have tried the same in Sql 2005. Options have been added in the list but add button turned grey. Any idea how to fix this?

  9. Anonymous says:

    Do you know how to get the row count without displaying the report?

  10. Not via a perfmon counter. You’ll either need to write a query to do this, or deliver the report to the null delivery extension and check the ExecutionLog table to see the # of rows.

  11. Vipul says:

    Is it applicable to SQL Server 2008 Reporting Services too? Is there similar article for 2008?  

  12. Penglv@live.com says:

    good knowledge shared. tks!

  13. sach says:

    Hello,

    nice way to get these in perfmon, BUT this whole information is also available in the ExecutionLog table of report catalog.

  14. wholesale snapback hats says:

    You are a great fan of <a href="http://www.snapbackswholesale.net">wholesale snapback hats</a> and wish to possess a few hats that your favorite athlete wears or your rapper flaunts in his new music video. You don't want to buy cheap snapback hats that are available in the market or with some vendor. You want to buy authentic hats, beanie hats or any headgear that is from a popular brand. However the cost of the hats often makes you feel low as you cannot manage the cost of the hats. Dear friend, don't lose heart, you can easily find popular <a href="http://www.snapbackswholesale.net">cheap wholesale snapback hats</a> at cost effective prices. Only that you need to find them at the right sources!