SSRS tuning Query–Part II October 22nd, 2013

Vinod Kumar

This is in continuation to SSRS tuning Query–Part I blog post. In this blog post I will bring couple more queries based on ExecutionLog2 View inside Reporting services Database. The first query is to find out the highest average RowCount per report execution. This is a report execution statistics.

SELECT TOP 10 instancename,
Avg(Datediff(s, timestart, timeend)) AS AvgReportDuration_seconds,
Avg(bytecount)                       AS AvgByteCount,
Avg([rowcount])                      AS AvgRowCount
FROM   executionlog2
WHERE  source = ‘Live’
GROUP  BY instancename,
ORDER  BY avgrowcount DESC 

In production environments, we want to find out which of the reports failed in their execution? The following query gives us the number of times a report execution failed.

SELECT instancename,
Count(*) AS NumberOfFailures
FROM   executionlog2
WHERE  [status] <> ‘rsSuccess’
GROUP  BY instancename,

The next query is identifying the most resource consuming report from our Reporting Services environment. This is one the most used query when looking at resource crunched environments.

Isnull(additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Pagination)[1]‘,  ‘integer’), 0) + Isnull(additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Rendering)[1]‘,  ‘integer’), 0) + Isnull(additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Processing)[1]‘,  ‘integer’), 0) AS EstimatedMemoryUsageKB,
‘integer’), 0) +  Isnull(additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Rendering)[1]‘,
‘integer’), 0) +  Isnull(additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Processing)[1]‘,
‘integer’), 0) AS ScalabilityTime_ms
FROM   executionlog2
WHERE  reportaction = ‘Render’
AND format = ‘RPL’
ORDER  BY estimatedmemoryusagekb DESC,
scalabilitytime_ms DESC 

We will keep sharing many more of such queries that I use in day-today at my work in future too. Do let me know if you would like these kind of blog posts for future too.

Share this article

Tags: , , , , , , ,

This entry was posted on Tuesday, October 22nd, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

8 Responses to “SSRS tuning Query–Part II”

  1. Phaneendra says:

    Hi Vinod,

    I keep working on performance tuning related activities frequently. These tips would be of great help. I keep these scripts at one place which are written by myself as well as the ones which are captured from various SQL Server forums. Please do keep sharing these kind of information as it will be useful for several folks like me. Thanks for sharing.


  2. Srinu Kore says:

    Hi Vinod,
    Please share such important queries in future tooo….

    Srinu Kore.

  3. Manish says:

    Sir i have a doubt that what kind of data we have to insert in executionlog2 and when.

    It will be very help full if you elaborate little bit or suggest some your other blog link.


    • Vinod Kumar says:

      Manish, Thanks for your comments. We don’t insert into executionlog2 table. It is done by SSRS infrastructure automatically. It is for us to troubleshoot using that data. I will write more about them in future posts maybe.

      • Manish says:

        But sir due to this when i am using your query than showing error for executionlog2

        Msg 208, Level 16, State 1, Line 1
        Invalid object name ‘executionlog2′.

        Sorry sir for silly question. But i am not getting properly this
        So please help


        • Vinod Kumar says:

          You need to run this on a SQL Server box which has SQL Server Reporting Services Installed. Connect to the ReportServer DB to do the analysis.

Leave a Reply