Investigating the performance of your SSRS reports

SQL Server Reporting Services keeps a nice view around for looking at the performance stats – ExecutionLog2 (new to 2008, obviously the ExecutionLog view predated it 🙂

Before you can optimize particular reports or your entire system, you need metrics and understand what they tell you.  In this posting, I want to focus on how to effectively interpret and utilize the data present in the new ExecutionLog2 view in the Reporting Services 2008 catalog database.  In summary, I am covering the following topics:

  • Description of ExecutionLog2 columns, with tips on how to interpret values
  • ExecutionLog2.AdditionalInfo and some interesting pieces of information it provides
  • Tips for analyzing ExecutionLog2 information
  • Tips for optimizing reports

When you go to query it, just keep in mind that Parameters is an ntext column, so you’ll want to use ‘like’ instead of trying to ‘=’ with a particular string.  Not sure what they didn’t make it nvarchar(max) since that was introduced in 2005, although it may be just to minimize the change between the 2 views.

If you’d used the ExecutionLog view that predates this one, you’re likely to appreciate the case statements they added in the view definition of ExecutionLog2 to change the RequestType and Source to human-readable values 🙂

Also note that Robert’s blog has tons of good info on SSRS in general and performance in particular – go subscribe 🙂