SSRS Identifying Reports to Tune – Top 10 Offenders

To discover reports which are candidates for tuning I use the following query to identify the reports which take the longest time to perform their data retrieval tasks.

The query I use is

DECLARE @ReportPath VARCHAR(MAX)
SET @ReportPath = '%'


SELECT TOP 10
c.[Path],c.[Name]
,[ReportID]
,COUNT(1) as UseCount
,SUM([TimeDataRetrieval]) as [TimeDataRetrieval]
,SUM([TimeProcessing]) as [TimeProcessing]
,SUM([TimeRendering]) as [TimeRendering]
,(SUM([TimeDataRetrieval])/(SUM([TimeDataRetrieval]+[TimeProcessing]+[TimeRendering])*1.00)) *100 as TimeDataRetrieval_TotalTime
,(SUM([TimeProcessing])   /(SUM([TimeDataRetrieval]+[TimeProcessing]+[TimeRendering])*1.00)) *100 as TimeProcessing_TotalTime
,(SUM([TimeRendering])    /(SUM([TimeDataRetrieval]+[TimeProcessing]+[TimeRendering])*1.00)) *100 as TimeRendering_TotalTime
,SUM([TimeDataRetrieval]+[TimeProcessing]+[TimeRendering]) as TotalTime
,SUM([ByteCount]) as [ByteCount]
,SUM([RowCount]) as [RowCount]
,MIN([TimeStart]) as FirstUsed
,MAX([TimeStart]) as LastUsed
,AVG([TimeDataRetrieval]*1.00)  as [Avg_TimeDataRetrieval]
,AVG([TimeProcessing]*1.00)        as [Avg_TimeProcessing]
,AVG([TimeRendering]*1.00)        as [Avg_TimeRendering]
,AVG([RowCount])                as [Avg_RowCount]
,AVG([TimeDataRetrieval]/(case when [RowCount] = 0 then 0.01 else [RowCount] end *1.00))  as [Avg_TimeDataRetrieval_Per_Row]  -- rudimentary hack to prevent divide by 0 and also geta number even when no data returned.
FROM
[dbo].[catalog] c WITH(NOLOCK)
LEFT OUTER JOIN [dbo].[ExecutionLog] el  WITH(NOLOCK) ON (c.ItemID = el.ReportID)
WHERE
1=1
-- AND c.[Path] LIKE @ReportPath
GROUP BY c.[Path],c.[Name],[ReportID]
HAVING COUNT(1) > 5
ORDER BY
SUM([TimeDataRetrieval]+[TimeProcessing]+[TimeRendering]) DESC,AVG([TimeDataRetrieval]) DESC

This gives me a list of the reports on the server in the order of longest time taken for data retrieval. Once I have this list, I then look at the reports and see what I can do to make the retrieval of each data set faster / more efficient by reviewing execution plans, and re-writing the queries / stored procedures.

Advertisements

One thought on “SSRS Identifying Reports to Tune – Top 10 Offenders

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s