SSRS Consultant Tip: Using the Execution Log to find slow running SSRS Report

A common question from clients new and old is why are my SSRS reports running slowly?

We have several tips about SSRS report development, starting with understanding what SSRS does well and what it doesn’t.

When you need clarity and not just random complaints, you need to know where to start.  And the answer is the Execution Log.  Or more  precisely, the ReportServer database view dbo.ExecutionLog3.   This view will show everything that’s been in SSRS for the last 90 days.  Here’s the view and it’s layout:

There are many things you can look at it.  But to start you want to see which reports are taking the most time.   Here’s a quick query which can get you started:

use ReportServer
go

select 
el3.InstanceName,
el3.ItemPath,
el3.TimeDataRetrieval,
el3.TimeProcessing,
el3.TimeRendering,
el3.TimeDataRetrieval + 
el3.TimeProcessing+ 
el3.TimeRendering as TotalTime
from dbo.ExecutionLog3 el3
where el3.Format = 'RPL'
order by TotalTime descCode language: JavaScript (javascript)

and here’s sample output:

The problem here is that if users run the same reports over and over (which is fairly common), you’ll get multiple records for each report.

So, lets’ update the query to show averages.  And while we’re at it, we want to know how often a report is run.  You have limited time. If something has run once a quarter, it’s not high priority to optimize.*

Here’s the query:

select 
el3.InstanceName,
el3.ItemPath,
avg(el3.TimeDataRetrieval) as AvgTimeDataRetrieval,
avg(el3.TimeProcessing) as AvgTimeProcessing,
avg(el3.TimeRendering) as AvgTimeRendering,
avg(el3.TimeDataRetrieval + 
el3.TimeProcessing+ 
el3.TimeRendering) as AvgTotalTime,
count(*) as NumberTimesRun
from dbo.ExecutionLog3 el3
where el3.Format = 'RPL'
group by el3.InstanceName,
el3.ItemPath
order by AvgTotalTime desc
Code language: JavaScript (javascript)

And here’s the result:

Some notes on what we’ve done – and what we’d do next:

1. Format = RPL
   This query shows only Format = RPL. This represents the time it takes from when you Refresh until the report shows in your web browser.
   The other types you’ll see fall into two categories.
   First, NULL (or no value) indicates a shared dataset.  We generally only used shared datasets for parameters and they are generally not an issue.  And if they’re an issue, they’re an issue globally.
   Next, EXCELOPENXML, PDF and CSV indicate the time required to produce the actual format.  This is pretty much a direct function of how big the report is so it’s not something we’re interested in at first pass.
   
2. Data Retrieval time much greater than processing time
   If you have a large data retrieval time and a small processing time, that indicates that you may have a SQL issue.   For example, in running this report, the first report listed took 8 seconds to retrieve data and only 8 milliseconds to render.  As it’s only been run once in 90 days, we’re not going to worry about that.
    
3. Processing Time great than Data Retrieval time
   This might be an issue.  Again, the times here aren’t large.  But often when processing time is big when compared to data retrieval, it may mean that too much data is being pulled in and then manipulated in SSRS. Which is not a good idea.
   
   

Again, this is just the start.  It tells you where the problems may lie so you can get to work.

If you’re interested in more help with the execution log or performance in general, please get in touch.