Reporting Service Execution Logging
- The Execution Log contains information of Report that executes across multiple servers in a scale-out (also in non scale out scenarios)
- A cap can be placed on the number of days of logging information kept
– This process is called ‘pruning’ and occurs each day at 2 am
Aimed at:
– Report Server administrators looking to identify reports that need to be cached in some manner
– Report Publishers looking to improve performance of reports: queries, layouts, and expressions
Information is stored a table called ExecutionLog in the ReportServer database. Important columns include:
- Name of the Report
- Rendering Format
- Parameter Values
- Time spent on data retrieval,processing and Rendering
- Source of data ( live or cached or snapshot)
- Do not query the table directly without nolock hint as it could cause concurrency problems.
- To review execution logging data, the information must be extracted and queried – DTS package and sample reports are shipped to help facilitate this process. (Check the Reporting Services Books online for step by step process)
ExecutionLog Table
reportID and userID |
Identifier to the report and user. |
| Format | Requested rendering format |
| parameters | null is report parameters were not used |
| timedataretrieval | Time (ms) spent retrieving data. |
| timeprocessing | Time (ms) spent processing. |
| timerendering | Time(ms) spent rendering. |
| source | 1=Live,2=Cache,3=Snapshot,4=History |
| status | Completed status, rsSuccess or error code. |
SQL Statement which will give the Name of the REPORT and its Execution details (For 2005):
Select CAST(C.Name AS VARCHAR(20)) [Name],
E.ReportID,
E.InstanceName,
E.UserName,
E.RequestType,
E.Format,
E.Parameters,
E.TimeStart,
E.TimeEnd,
E.TimeDataRetrieval,
E.TimeProcessing,
E.TimeRendering,
E.Source,
E.Status,
E.ByteCount,
E.[RowCount]
from executionlog E WITH (NOLOCK) inner join catalog C WITH (NOLOCK)
on E.ReportID = C.ItemID
Try out the difference between the above and this one:
select * from executionlog WITH (NOLOCK)
SQL Statement which will give the Name of the REPORT and its Execution details (For 2008):
Select CAST(C.Name AS VARCHAR(20)) [Name],
E.ReportID,
E.InstanceName,
E.UserName,
E.RequestType,
E.Format,
E.Parameters,
E.TimeStart,
E.TimeEnd,
E.TimeDataRetrieval,
E.TimeProcessing,
E.TimeRendering,
E.Source,
E.Status,
E.ByteCount,
E.[RowCount], E.[AdditionalInfo] from executionlogstorage E WITH (NOLOCK) inner join catalog C WITH (NOLOCK) on E.ReportID = C.ItemID
More Information: