SSRS tuning Query–Part I October 21st, 2013

Vinod Kumar

I have a number of these queries that I use as part of my performance tuning exercise at MTC. Out of those queries that I run, here are couple of them that I use to troubleshoot issues when working with SSRS performance.

– Query to fetch the reports that were executed against the report server

SELECT (C.path + C.name) AS ReportName,

    ES.username,

    ES.parameters,

    ES.timestart,

    ES.timeend,

    ES.timedataretrieval,

    ES.timerendering,

    ES.timeprocessing,

    ES.[rowcount],

    ES.additionalinfo

FROM   Executionlogstorage ES

INNER JOIN dbo.Catalog C

ON C.itemid = ES.reportid

And a small extension to the first query is the below query which gives a finite top-10 list to start out tuning activity from. Do you have similar

– Query to fetch the TOP 10 reports that took the longest to execute

SELECT TOP 10 (C.path + C.name) AS ReportName,

    ES.username,

    ES.parameters,

    Datediff(s, timestart, timeend) AS ReportExecutionDuration_Seconds,

    ES.timestart,

    ES.timeend,

    ES.timedataretrieval,

    ES.timerendering,

    ES.timeprocessing,

    ES.[rowcount],

    ES.additionalinfo

FROM   Executionlogstorage ES

INNER JOIN dbo.Catalog C

ON C.itemid = ES.reportid

ORDER  BY reportexecutionduration_seconds DESC

The second query is something I use almost every single time – fundamentally this query gives us the answer to “Where to start?” question. Each of these give us idea of Execution Duration, Rendering time, Processing time and these are very important values to look out for.

Share this article

Tags: , , , , , , ,

This entry was posted on Monday, October 21st, 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.

Leave a Reply



 

Email
Print