SSRS: Report Server Usage Report

While working with SSRS (SQL Server Reporting Service) it is very common to look into the Execution log to understand the usage pattern of the report that are deployed or to troubleshoot the performance issues.SSRS stores the detailed execution plan in a table named ExecutionLog which resides in the SSRS repository database.

Below script will give you the details of report execution in last 24 hours.

SELECT c.path,c.name,
CASE 
   WHEN el.RequestType = 0 THEN 'Interactive'
   WHEN el.RequestType = 1 THEN 'Subscription'
   WHEN el.RequestType = 2 THEN 'Refresh Cache'END AS RequestType,  
 el.Format,
el.TimeStart,
el.TimeEnd,
DATEDIFF(ss,el.TimeStart,el.TimeEnd) AS 'TotalDuration(Sec)',
(el.TimeDataRetrieval/1000.00) AS 'Data Retrieval Time (Sec)',
(el.Timeprocessing/1000.00) AS 'Processing Time(Sec)',
(el.TimeRendering/1000.00) AS 'Rendering Time(Sec)',
CASE 
WHEN el.Source=1 THEN 'LIVE'
WHEN el.Source=2 THEN 'Cache'
WHEN el.Source=3 THEN 'Snapshot'
WHEN el.Source=4 THEN 'History'
WHEN el.Source=5 THEN 'Ad hoc(Report Builder)'
WHEN el.Source=6 THEN 'Session'
WHEN el.Source=7 THEN 'Report Definition Customization Extension(RDCE)'
END AS 'Source',
el.status,
el.ByteCount/1024.00 AS 'Size(Kb)',
el.[RowCount] AS 'Number of Records'
FROM ExecutionLog EL INNER JOIN Catalog cON c.itemid=el.reportid
WHERE TimeStart>= DATEADD(hh,-24,GETDATE())ORDER BY TimeStart DESCLet us look into the output columns :



• Path : Report path in the the reports manager
• Name : Report Name
• Request Type :This tells you how the report got executed . Interactive is the manual execution of the report using the report manager or a custom UI. Report executed as part of scheduled subscription will have value subscription for this column.
• Format : The rendering format
• TimeStart : Report process start time
• TimeEnd : Report process end time . The difference between the TimeStart and TImeEnd gives you the total duration of the request.
• Data Retrieval time (Sec) : The number of seconds spent on data sources and data extenstions in main report and all of its subreports. If the multiple data source are accessed parallel, this will be be the duration of the longest data set  duration.If the data sources are accessed sequentially, this will be the sum of all data set duration.
• Processing Time (Sec) : The number of seconds spent in the processing the request. This include report processing bootstrap time and processing time for grouping,sorting,filtering ,aggregation ,subreport processing etc.
• Rendering Time : The number of seconds spent on rendering extension.This includes time spent on rendering, pagination module, on demand expression evaluation etc.
• Source :Specifies the type of execution.
• Live : data set  queries are executed to get the result
• Cache: Reports is generated based on the data in the cache. Data set queries are not excuted
• Session :Subsequent request with an existing session .For example, initial request to view the report and subsequent request is to export to pdf.
• RDCE: Indicates Report Definition Customization Extension, that can dynamically customize a report definition before it is passed to the processing engine during the report execution
• Status : Report execution status . rssuccess denote the success of report execution.
• Size(KB) : Number of Kb generated as output of this report execution request.
• Number of Records:Number of rows processed.

By execution log retention period is 60 days. If your environment requires a change on this value , you can modify this through the advanced page of report server properties after connecting the SSRS in the SSMS. Refer the technet page.The other option is change the value directly in the configuration table.

SELECT * FROM ConfigurationInfo WHERE Name = 'ExecutionLogDaysKept'



If you liked this post, do like my page on FaceBook