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

• Anonymous
  July 26, 2005
  Altova Releases XML Engines To Developers
  An ASP.NET Custom Control for Dynamically Filtering Data...

• Anonymous
  July 31, 2005
  
  Secret SQL Reporting Services Perfmon counters uncovered!
  
  
  Scott C. Reynolds has a great comeback...

• Anonymous
  December 01, 2005
  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!

• Anonymous
  December 02, 2005
  The comment has been removed

• Anonymous
  March 22, 2006
  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!

• Anonymous
  March 23, 2006
  I've never seen this. Are you able to add the other SSRS counters?

• Anonymous
  March 27, 2006
  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?
  

• Anonymous
  March 27, 2006
  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?
  

• Anonymous
  August 07, 2006
  Do you know how to get the row count without displaying the report?

• Anonymous
  August 07, 2006
  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.

• Anonymous
  June 08, 2009
  PingBack from http://toenailfungusite.info/story.php?id=1249

• Anonymous
  February 24, 2010
  Is it applicable to SQL Server 2008 Reporting Services too? Is there similar article for 2008?  

• Anonymous
  March 09, 2010
  good knowledge shared. tks!

• Anonymous
  July 28, 2010
  Hello, nice way to get these in perfmon, BUT this whole information is also available in the ExecutionLog table of report catalog.

• Anonymous
  April 12, 2012
  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!