Monitoring SSRS report performance: Part 1

One of my self-assigned tasks is to monitor the performance of our reports. It’s a good way to see how reports behave over time or what effect changes on a report has. There’s a few methods I use, from seeing what report is running right now to how that report compares to other report runs.

Reporting Services saves report execution statistics in a table called ExecutionLog in the ReportServer database on your report server. In SQL 2008 there’s also a view called ExecutionLog2 that does a few joins and shows the values in a way that may make more sense. I’m not going to go into too much detail about that view or table here, they’ve been written up pretty well in posts by others. See the references section at the end for a few helpful links.

The only problem with the ExecutionLog table is that it doesn’t show a row for a report until the report execution is complete. It doesn’t help you to see what report is running at a particular time. There are times when the report server is under load that I want to track what reports are running at that exact time. To do that I wrote a small VB.NET program that will show me what’s running anytime I choose. I wrote mine in Visual Studio 2010 but it could be created in earlier versions, and you can easily convert this to C# if you wish.

To start, create a new Windows Forms project called Current Reports, create 2 forms, set one up to enter login information, and drop a data grid with a label for each report server you want to monitor, a timer control, and a button or the other. Next create a web reference to a SQL2008 report server and call it ReportService:

   1: http://localhost/ReportServer_Reporting/ReportService2005.asmx

Then set a reference to the report service, one for each server you’re monitoring. Pass in the login information from your login form.

   1: Imports Current_Reports.ReportService

   2:  

   3: Public rsServer1 As New ReportingService2005

   4:  

   5: rsServer1.Url = "http://localhost:85/ReportServer_Reporting/ReportService2005.asmx"

   6: rsServer1.Credentials = New Net.NetworkCredential(UserName, Password, Domain)

Finally, write a procedure to fetch the information you want to see. I call the procedure when I first load the form, again when the timer fires (I set the timer interval to 30 seconds), or on demand when I click the refresh button.

   1: Public Sub ListRunningJobs()

   2:     dgvServer1.Rows.Clear()

   3:    

   4:     Dim Server1Jobs As Job() = Nothing

   5:     Server1Jobs = rsServer1.ListJobs()

   6:     Dim job1 As Job

   7:  

   8:     For Each job1 In Server1Jobs

   9:         If job1.Status = JobStatusEnum.Running Or job1.Status = JobStatusEnum.New Then

  10:             Dim strValues(3) As String

  11:             strValues(0) = job1.Name

  12:             strValues(1) = job1.JobID

  13:             strValues(2) = job1.StartDateTime

  14:             strValues(3) = job1.User

  15:             dgvServer1.Rows.Add(strValues)

  16:         End If

  17:     Next job1

  18:  

  19:     Me.Text = "Currently Running Reports as of " & Date.Now.ToString

  20:     

  21: End Sub  'ListRunningJobs

This code just checks to server for any running jobs. For each job it finds it returns the report name, the job id, when it started, and who’s running it. It also shows the time the job info was fetched, so you can see if there are any reports that have been running for a long time. You need to duplicate this for each server you want to monitor; in monitoring two so that’s why I used two data grids.

CurrentReports

In Part 2 I’ll describe how I check the execution logs.

References:

Robert Bruckner has a great blog on Report Server performance. This post is a great description of the ExecutionLog2 view.

ExecutionLog2 View – Analyzing and Optimizing Reports