SSRS Report Execution Statistics and Analysis

13 Nov
November 13, 2011

Understanding and analyzing your SSRS report execution statistics is critical if you are serious about optimizing your SSRS reports, over the years working with SSRS I have found and developed a few procedures that are helpful in analyzing problematic report (in terms of execution time).

This post will go through some of the procedures you could use to look at your SSRS reports execution times.

In an older post I have gone through the 3 stages an SSRS report goes through when being executed, you can checkout my post on SSRS Execution Time Types and Optimization for more details.

The table you want to look at in the SSRS catalog database for performance statistics is the ExecutionLogStorage table. The columns in that table are self-explanatory, one thing to note is that the TimeDataRetrieval, TimeProcessing and TimeRendering are expressed in millisecond.

I like to use the following query to get some meaningful top-line statistics about my report’s performance.

USE [ReportServer$<instance-name>]

SELECT
    CASE WHEN ReportID = '<report-unique-id-1>' THEN 'Report Type 1' ELSE
    CASE WHEN ReportID = '<report-unique-id-2>' THEN 'Report Type 2' ELSE
    CONVERT(nvarchar(100),ReportID) END END as ReportName,
    CONVERT(date,[TimeStart]) as StartDate,
    AVG(TimeDataRetrieval)/1000 as AvgTimeDataRetrieval,
    AVG(TimeProcessing)/1000 as AvgTimeProcessing,
    AVG(TimeRendering)/1000 as AvgTimeRendering,
    AVG(TimeDataRetrieval + TimeProcessing + TimeRendering)/1000 as AvgTotalTime
    ,COUNT(*) as NumOfExecution
FROM [ExecutionLogStorage]
GROUP BY
    ReportID,
    CONVERT(date,[TimeStart])
ORDER BY
    CONVERT(date,[TimeStart]) DESC,
    AVG(TimeDataRetrieval + TimeProcessing + TimeRendering) DESC

The query will essentially give you back which SSRS reports are taken the longest to run on a day by day basis. This will probably not catch monthly, quarterly or annual execution “peaks”, but you can play around with the query and edit it according to your reporting cycles.

The ReportName column in the output of the query has a SQL Server CASE statement, this is because the ExecutionLogStorage table has “Uniqueidentifier” data type for the Report ID, and I’ve not managed to find a table that links Report IDs to Report Names. I usually just put a case statement around the reports am trying to analyse over the next couple of weeks to make them easier to identify.

Now although this is very cool, you can get even cooler, for example, you can find out if a particular report has been run with the same parameter more than once, and if so, you can make executive decisions regarding which SSRS reports you could cache or snapshot, and what parameters you should set them up with. The query below demonstrates this.

USE [ReportServer$<instance-name>]

SELECT
    CASE WHEN ReportID = '<report-unique-id-1>' THEN 'Report Type 1' ELSE
    CASE WHEN ReportID = '<report-unique-id-2>' THEN 'Report Type 2' ELSE
    CONVERT(nvarchar(100),ReportID) END END as ReportName,
    CONVERT(nvarchar(1000),[Parameters]) as Parameters,
    MAX(CONVERT(date,[TimeStart])) as LastRun,
    AVG(TimeDataRetrieval)/1000 as AvgTimeDataRetrieval,
    AVG(TimeProcessing)/1000 as AvgTimeProcessing,
    AVG(TimeRendering)/1000 as AvgTimeRendering,
    AVG(TimeDataRetrieval + TimeProcessing + TimeRendering)/1000 as AvgTotalTime
    ,COUNT(*) as NumOfExecution
FROM [ExecutionLogStorage]
WHERE
    CONVERT(date,[TimeStart]) >= '20110101'
GROUP BY
    ReportID,
    CONVERT(nvarchar(1000),[Parameters])
ORDER BY  
    COUNT(*) DESC,
    AVG(TimeDataRetrieval + TimeProcessing + TimeRendering) DESC

I really recommend playing around with that table to better understand how your SSRS reports are behaving in terms of execution time and general health, for example an over-all high “Processing Time” could indicate that the SQL Server Reporting Service server is being over utilized in terms of resources.

          0 votes
2 replies
  1. Mark Hudson from Sydney, AU says:

    The Report NAME is contained in the Catalog table, along with other “Items” such as DataSources, Shared Datasets etc.

    Updated query:

    SELECT
    c.Name AS ReportName,
    CONVERT(nvarchar(1000),[Parameters]) as Parameters,
    MAX(CONVERT(date,[TimeStart])) as LastRun,
    AVG(TimeDataRetrieval)/1000 as AvgTimeDataRetrieval,
    AVG(TimeProcessing)/1000 as AvgTimeProcessing,
    AVG(TimeRendering)/1000 as AvgTimeRendering,
    AVG(TimeDataRetrieval + TimeProcessing + TimeRendering)/1000 as AvgTotalTime
    ,COUNT(*) as NumOfExecution
    FROM [ExecutionLogStorage] els
    INNER JOIN [Catalog] c ON els.ReportID=c.ItemID
    WHERE
    CONVERT(date,[TimeStart]) >= ‘20110101’
    GROUP BY
    c.Name,CONVERT(nvarchar(1000),[Parameters])
    ORDER BY
    COUNT(*) DESC,
    AVG(TimeDataRetrieval + TimeProcessing + TimeRendering) DESC

    Reply
    • admin says:

      Thanks Mark, nice update.

      I’ll integrate your query with the Catalog table back into the original article, with the appropriate accreditation.

      Cheers

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply to admin Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>