SQL to list retrieval, processing and rendering duration times of SSRS reports executions

 --Transact-SQL to list retrieval, processing and rendering duration times of SSRS reports executions.
/*
The most often complain of users is "My report is running that slowly, why?" and when you run it as admin, it works normal.
With this Transact-SQL script you can check the execution durations of report splitted in data retrieval, processing and rendering durations to analyse which part takes to most time and if it's really slow.
Please remark: Querying the ReportServer database directly is not a supported way.
Works with SQL Server 2005 / Reporting Services 2005 and higher version in all editions.
Requires select permissions on the ReportServer database.
*/

-- List processing duration times of SSRS reports executions.
DECLARE @begin datetime, @end datetime, @minDuration int;

-- Include executions of the last month.
SET @begin = DATEADD(m, -1, GETDATE());
SET @end   = GETDATE();
SET @minDuration = 3000;  -- min Duration in Millisecondes = 3 sec.

;WITH ExeLog AS

   -- Basic CTE query for ExecutionLogStore
   (SELECT CTL.[Path] AS ReportPath
      ,ELS.Format
  ,ELS.TimeStart
  ,ELS.TimeEnd
  ,DATEDIFF(ms, ELS.TimeStart, ELS.TimeEnd) AS TimeDuration
  ,ELS.TimeDataRetrieval
  ,ELS.TimeProcessing
  ,ELS.TimeRendering
  ,ELS.TimeDataRetrieval + ELS.TimeProcessing + ELS.TimeRendering AS TimeTotal
  ,ELS.ByteCount
  ,ELS.[RowCount] AS RwCount
FROM dbo.ExecutionLogStorage AS ELS
INNER JOIN
dbo.[Catalog] AS CTL
ON ELS.ReportID = CTL.ItemID
    WHERE ELS.TimeStart BETWEEN @begin AND @end
          AND DATEDIFF(ms, ELS.TimeStart, ELS.TimeEnd) >= @minDuration
          AND ELS.[Status] = 'rsSuccess')

SELECT ExeLog.ReportPath
      ,ExeLog.Format
      ,ExeLog.ByteCount
      ,ExeLog.RwCount
      ,ExeLog.TimeStart
      ,ExeLog.TimeDuration AS Duration
      ,ExeLog.TimeTotal AS Total
      ,ExeLog.TimeDataRetrieval AS DataRetrieval
      ,ExeLog.TimeProcessing AS Processing
      ,ExeLog.TimeRendering AS Rendering
       -- Percentage related to TimeTotal
      ,CASE WHEN ExeLog.TimeTotal = 0 THEN 0.0
            ELSE CONVERT(numeric(4, 2), (100.0 * ExeLog.TimeDataRetrieval / ExeLog.TimeTotal)) END AS [Data%]
      ,CASE WHEN ExeLog.TimeTotal = 0 THEN 0.0
            ELSE CONVERT(numeric(4, 2), (100.0 * ExeLog.TimeProcessing / ExeLog.TimeTotal)) END AS [Proc%]
      ,CASE WHEN ExeLog.TimeTotal = 0 THEN 0.0
            ELSE CONVERT(numeric(4, 2), (100.0 * ExeLog.TimeRendering / ExeLog.TimeTotal)) END AS [Rend%]
FROM ExeLog
ORDER BY ExeLog.TimeDuration DESC;