Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

SQL Server Reporting Services Reports Performance Debugging and Analysis


By:   |   Last Updated: 2011-03-14   |   Comments (4)   |   Related Tips: > Reporting Services Performance

Problem

SSRS provides a very user friendly way to author and deploy reports. These reports can be accessed from different platforms where the reports are deployed - reports manager, SharePoint, stand-alone / distributed applications or programmatically using SSRS SOAP endpoints. Unfortunately, SSRS / BIDS does not provide any high-end debugging tools such as SQL Profiler for analyzing the performance of SSRS reports. In this tip we will look at different ways of debugging and analyzing SSRS reports performance using execution logs and freeware tools.

Solution

Two of the main areas which can provide insight into the performance of a report are SSRS reports execution logs and a Web proxy. The execution logs is pretty obvious, but you maybe wondering why a web / http proxy. SSRS Reports are mostly deployed on a platform like SharePoint or are accessed by applications over the network. Often it may be that the hosting platform or the network bandwidth is the cause of the performance bottleneck. Analyzing the entire life of the report execution along with the type of content exchanged between the client and server can provide details of each activity that happens during the report execution.

Execution Logs

SSRS provides a set of standard views to query the report execution logs. You will find three execution log views in the ReportServer database. If you are using SSRS 2008 R2, you should use view "ExecutionLog3", because the rest of the views are for backward compatibility.

Let's go through an example to better understand this mechanism. I have created a report and intentionally configured the stored procedure query to wait for 25 seconds.

Now log on the "ReportServer" database and query the ExecutionLog3 view and you should be able to find the statistics of the report just executed. The below screenshot shows the statistics of the report I executed on my machine. If you look carefully at the "TimeDataRetrieval" field, you will see the time taken for retrieving data is approx 25 seconds which is just what I had configured in my stored procedure. TimeDataRetrieval + TimeProcessing + TimeRendering should be almost equal to the actual time taken for the report request. You can learn more about each of these fields here.

different ways of debugging and analyzing ssrs reports

As far as debugging and analyzing a single report, this mechanism is okay. But if you want to analyze a huge volume of reports on your report server, manually analyzing the logs for each report is time consuming and inefficient. Some framework that automates this process is required. Fortunately, you do not need to develop it from scratch, because on Codeplex there are two such frameworks that are available:


Web / HTTP Proxy

Every application that gets accessed over the network using HTTP or HTTPS as the communication protocol, needs a web proxy sooner or later for request and response analysis over the network. One such great FREE web proxy tool is Fiddler.

Download and install Fiddler. Now execute your report from reports manager and check out how Fiddler profiles the exchange between the client and server. It has a view that is of very high value for the report analysis - the "Timeline" tab. This tab shows which content took what time to download and render. Many times you will find that report execution took little time, still the report is taking a long time to render. You can analyze the traffic using Fiddler and you might end up with the observation that a huge ".gif" file which is a part of the report takes a long time to download and render, which causes the report rendering to be slow.

Below is a screenshot of the timeline analyzed by Fiddler for the report I executed. You can make out that the timeline shown here is similar to what is shown in the ExecutionLogs. The difference here is that it shows which content and request took what amount of time. There is a lot more analysis that you can do with Fiddler, and I leave it up to the reader to explore this tool as per their needs.

install the frameworks to analyze your ssrs reports execution logs

Next Steps


Last Updated: 2011-03-14


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, February 28, 2017 - 8:24:26 PM - Nitin Surana Back To Top

Please mention that the user needs to use "ReportServer" before trying to query ExecutionLog3.

use ReportServer


Saturday, August 11, 2012 - 7:20:59 AM - Neelam Back To Top

Nice article


Monday, November 21, 2011 - 3:26:53 AM - Feodor Back To Top

Hello, I just developed a SSRS Dashboard reports tool which can be run as SSMS Custom reports to the Report Server database and shows config, security and performance information for the SSRS.


The tool can be downloaded here: http://sqlconcept.com/tools/ssrs-dashboard/


Monday, March 14, 2011 - 12:52:36 PM - Simon Back To Top

Thanks for this post, Siddhart. SCRUBS is exactly the type of tool that I'm looking for to monitor/optimize my SSRS environments. We are a small shop and I wear many hats, so this kind of managed solution is ideal. Does anyone else out there have any feedback/comments, re: SCRUBS?


Learn more about SQL Server tools