Report Server ExecutionLog SSRS

The report server execution log contains information about reports that are run.  The execution log can be very useful when trying to troubleshoot issues on the report server (such as ClockSkew and Blocked Process).  As a database administrator you can use this information and relay back to the report developers how they may improve performance of specific reports that are hindering the server’s performance.

There are three standard views for the execution log:

dbo.ExecutionLog – Available since 2000
dbo.ExecutionLog2 – Introduced in 2008
dbo.ExecutionLog3 – Introduced in 2008R2

I thought it might be of most use to break this blog up into 3 sections and dive into each of the columns within each view.  So let’s start with dbo.ExecutionLog!

dbo.ExecutionLog

Example Query:
SELECT *
FROM dbo.ExecutionLog E (NOLOCK)
WHERE
     TimeStart >= '9/18/2012'

Column Name

Description

InstanceName

Name of server that handled the request.

ReportID

Report ID can be joined to Catalog.ItemID to find the Report Name.

UserName

User that executed the report.

RequestType

Who requested the report:

System (subscriptions) also can be thought as any windows service process – 0

User, also can be thought as any web service process or interactive request– 1

Format

Rendered format, will be RPL when web service process.

Parameters

The parameters used on the report execution.

TimeStart

Start time of report.

TimeEnd

End time of report.

TimeDataRetrieval

Number of milliseconds to retrieve the data.

TimeProcessing

Number of milliseconds to process the report.

TimeRendering

Number of milliseconds to render the data or generate the output.

Source

Source of the report execution.

1: Live
2: Cache
3: Snapshot
4: History
5: Ad Hoc (i.e., Report Builder)
6: Session (i.e., another request within existing session)
7: RDCE (i.e., Report Definition Customization Extension) 

Status

rsSuccess – Report completed successfully 
rsProcessingAborted
– Report failed
Error code – Report failed

If more than one error occurs, only the first is logged.

ByteCount

Size of the rendered report in Bytes.

RowCount

Number of rows the query or queries return. 

 

dbo.ExecutionLog2

The difference between ExecutionLog2 and ExecutionLog is not much.  They view added a few new fields and renamed a few of the older ones.

Example Query:
SELECT *
FROM dbo.ExecutionLog E (NOLOCK)
WHERE
     TimeStart >= '9/18/2012'

Column Name

Description

InstanceName

Name of server that handled the request.

ReportPath

The path to the report.

UserName

User that executed the report.

ExecutionID

ID associated with the request.

RequestType

Subscription or Interactive

Format

Rendered format, will be RPL when web service process.  If null, the request is an interactive event such as a drill through or interactive sort.

Parameters

The parameters used on the report execution.

ReportAction

Render, Sort, BookMarkNavigation, DocumentNavigation, GetDocumentMap, Findstring

TimeStart

Start time of report.

TimeEnd

End time of report.

TimeDataRetrieval

Number of milliseconds to retrieve the data.  Includes time opening connection to
the data source and time spent reading rows from the data extension.

TimeProcessing

Number of milliseconds to process the report. Includes report and tablix processing.

TimeRendering

Number of milliseconds to render the data or generate the output.

Source

Source of the report execution.

1: Live
2: Cache
3: Snapshot
4: History
5: Ad Hoc (i.e., Report Builder)
6: Session (i.e., another request within existing session)
7: RDCE (i.e., Report Definition Customization Extension) 

Status

rsSuccess – Report completed successfully
rsProcessingAborted – Report failed
Error code – Report failed

If more than one error occurs, only the first is logged.

ByteCount

Size of the rendered report in Bytes.

RowCount

Number of rows the query or queries return.

AdditionalInfo

XML data about the execution.

dbo.ExecutionLog3

The difference between ExecutionLog3 and ExecutionLog2 is not much.  The ExecutionLog3 added additional diagnostic information inside the final column AdditionalInfo.  The AdditionalInfo column contains a 1:Many relationship.  Overall the fields structure is identical to ExecultionLog2.

Example Query:
SELECT *
FROM dbo.ExecutionLog E (NOLOCK)
WHERE
     TimeStart >= '9/18/2012'

Column Name

Description

InstanceName

Name of server that handled the request.

ReportPath

The path to the report.

UserName

User that executed the report.

ExecutionID

ID associated with the request.

RequestType

Subscription or Interactive

Format

Rendered format, will be RPL when web service process.  If null, the request is an interactive event such as a drill through or interactive sort.

Parameters

The parameters used on the report execution.

ReportAction

Render, Sort, BookMarkNavigation, DocumentNavigation, GetDocumentMap, Findstring

TimeStart

Start time of report.

TimeEnd

End time of report.

TimeDataRetrieval

Number of milliseconds to retrieve the data.  Includes time opening connection to
the data source and time spent reading rows from the data extension.

TimeProcessing

Number of milliseconds to process the report. Includes report and tablix processing.

TimeRendering

Number of milliseconds to render the data or generate the output.

Source

Source of the report execution.

1: Live
2: Cache
3: Snapshot
4: History
5: Ad Hoc (i.e., Report Builder)
6: Session (i.e., another request within existing session)
7: RDCE (i.e., Report Definition Customization Extension) 

Status

rsSuccess – Report completed successfully
rsProcessingAborted – Report failed
Error code – Report failed

If more than one error occurs, only the first is logged.

ByteCount

Size of the rendered report in Bytes.

RowCount

Number of rows the query or queries return.

AdditionalInfo

XML data about the execution.

More on the AdditionalInfo field
Typical Output:

<AdditionalInfo>
<ProcessingEngine>2</ProcessingEngine>
<ScalabilityTime>
<Pagination>0</Pagination>
<Processing>0</Processing>
</ScalabilityTime>
<EstimatedMemoryUsageKB>
<Pagination>44</Pagination>
<Processing>17</Processing>
</EstimatedMemoryUsageKB>
<DataExtension><SQL>1</SQL></DataExtension>
</AdditionalInfo>

<ProcessingEngine>2</ProcessingEngine>
This piece of the XML identifies the version of processing engine that was used for executing the report.
1 = Yukon Engine
2 = On-Demand Processing Engine

<ScalabilityTime>
<Pagination>0</Pagination>
<Processing>0</Processing>
</ScalabilityTime>
In this particular instance, the value 0 means that no time was spent doing any scalable operations.  In other words you have low memory usage (w00t!).  If the value is > 0, then that is the number of milliseconds spent doing the particular task.

<EstimatedMemoryUsageKB>
<Pagination>44</Pagination>
<Processing>17</Processing>
</EstimatedMemoryUsageKB>

This number indicates the memory usage for the data size dependent operations such as grouping, filtering, sorting, any aggregations, page layout and render operations.  Anything under 10000 may be acceptable, but you might want to look into any report that seems to be over 5000.

<DataExtension><SQL>1</SQL></DataExtension>
This identifies the data extensions and data sources that are utilized for the report.  The number in the middle tells you how many times the specific type of data source is queried.

This entry was posted in SQL Server 2008, SQL Server 2008 R2, SSRS. Bookmark the permalink.