Tuesday, January 14, 2014

Detailed Statistics for SSRS Reports

I've had this report for years but until today never really had a conversation about the statistical value you can find in the Reporting Services database.  When a user contacts  you and claims that a report is "slow", where do you start?  Do you fire up a profiler trace and ask them to run it again?  Do you open the report and examine the query's execution plan for optimization opportunities?  Perhaps you weren't aware, but there is some really good statistical information in the Reporting Services database.  I put together the following report to assist me with my SSRS tuning investigation and efforts.  You can get the RDL for the report here.

(Note #1: I pulled the margins in to hide some account and report path information - better to be safe than sorry!)


The query/dataset to populate the report:

SELECT TOP 1000
        UserName
       ,Name
       ,Path
       ,[Status]
       ,Format
       ,TimeStart
       ,TimeEnd
       ,ByteCount
       ,ByteCount / 1024 AS KB
       ,ByteCount / 1024 / 1024 AS MB
       ,[RowCount]
       ,TimeDataRetrieval
       ,TimeProcessing
       ,TimeRendering
       ,[Parameters]
FROM    [ReportServer$SQL01].dbo.ExecutionLog AS ex
       ,[ReportServer$SQL01].dbo.Catalog AS cat
WHERE   ex.ReportID = cat.ItemID
        AND Name NOT LIKE '_My Reports%'
        AND ( UserName = @UserName
              OR @UserName = '--- ALL USERS ---' )
        AND [Status] IN (@ReportStatus)
        AND ( [Name] = @ReportName
              OR @ReportName = '--- ALL REPORTS ---' )
ORDER BY TimeStart DESC