Use ExecutionLog and the ExecutionLog3 view in Reporting Services
The Reporting Services report server execution log contains information about the reports that execute on the server or on multiple servers. These servers are in a native mode scale-out deployment or a SharePoint farm. You can use the report execution log to find out:
- The number of times a report is requested.
- What output formats are most used.
- The processing time spent on each processing phase in milliseconds.
The log contains information on the length of time spent running a report's dataset query and the time spent processing the data. If you're a report server administrator, you can review the log information and identify long running tasks. You can also make suggestions to the report authors on the areas of the dataset or processing report they might be able to improve.
Report servers configured for SharePoint mode can also utilize the SharePoint Unified Logging Service (ULS) logs. For more information, see Turn on Reporting Services events for the SharePoint trace log (ULS)
View log information
The report server logs data about report execution into an internal database table. The information from the table is available from SQL Server views.
The report execution log is stored in the report server database that by default is named ReportServer. The SQL views provide the execution log information. The "2" and "3" views were added in more recent releases and contain new fields, or they contain fields with friendlier names than the previous releases. The older views remain in the product so custom applications that depend on them aren't impacted. If you don't have a dependence on an older view, for example ExecutionLog, you should use the most recent view, ExecutionLog3.
Configuration settings for a SharePoint mode report server
You can turn report execution logging on or off from the system settings of a Reporting Services service application.
By default, log entries are kept 60 days. Entries that exceed this date are removed at 2:00 A.M. every day. On a mature installation, only 60 days of information is available at any given time.
You can't set limits on the number of rows or on the type of entries that are logged.
Enable execution logging for a SharePoint server
From SharePoint Central Administration, select Manage service applications in the Application Management group.
Choose the name of the Reporting Services service application you want to configure.
Select System Settings.
Select Enable Execution Logging in the Logging section.
Select OK.
Enable verbose logging for a SharePoint server
You need to enable logging as described in the previous steps and then complete the following steps:
From the System Settings page of your Reporting Services services application, find the User-defined section.
Change the ExecutionLogLevel to verbose. This field is a text entry field and the two possible values are verbose and normal.
Configuration settings for a native mode report server
You can turn report execution logging on or off from the Server Properties page in SQL Server Management Studio. The EnableExecutionLogging is an advanced property.
By default, log entries are kept 60 days. Entries that exceed this date are removed at 2:00 A.M. every day. On a mature installation, only 60 days of information are available at any given time.
You can't set limits on the number of rows or on the type of entries that are logged.
Enable execution logging for a native mode server
Start SQL Server Management Studio with administrative privileges. For example, right-click the Management Studio icon and select Run as administrator.
Connect to the desired report server.
Right-click the server name and select Properties. If the Properties option is disabled, verify you ran SQL Server Management Studio with administrative privileges.
Select the Logging page.
Select Enable report execution Logging.
Enable verbose logging for a native mode server
You need to enable logging as described in the previous steps and then complete the following steps:
From the Server Properties dialog, select the Advanced page.
In the User-defined section, change the ExecutionLogLevel to verbose. This field is a text entry field and the two possible values are verbose and normal.
Log fields (ExecutionLog3)
This view adds more performance diagnostics nodes inside the XML based AdditionalInfo column. The AdditionalInfo column contains an XML structure of 1 to many fields of information. The following example shows a Transact SQL statement that retrieves rows from the view ExecutionLog3. The sample assumes the report server database is named ReportServer:
Use ReportServer
select * from ExecutionLog3 order by TimeStart DESC
The following table describes the data that's captured in the report execution log.
| Column | Description |
|---|---|
| InstanceName | Name of the report server instance that handled the request. If your environment has more than one report server, you can analyze the InstanceName distribution to monitor and determine if your network-load balancer distributes requests across report servers as expected. |
| ItemPath | Path of where a report or report item is stored. |
| UserName | User identifier. |
| ExecutionID | The internal identifier associated with a request. Requests on the same user sessions share the same execution ID. |
| RequestType | Possible Values: Interactive Subscription Analyzing log data filtered by RequestType=Subscription and sorted by TimeStart might reveal periods of heavy subscription usage and you might want to modify some of the report subscriptions to a different time. |
| Format | Rendering format. |
| Parameters | Parameter values used for a report execution. |
| ItemAction | Possible values:RenderSortBookMarkNavigationDocumentNavigationGetDocumentMapFindstringExecuteRenderEdit. |
| TimeStart | Start and stop times that indicate the duration of a report process. |
| TimeEnd | |
| TimeDataRetrieval | Number of milliseconds spent retrieving the data. |
| TimeProcessing | Number of milliseconds spent processing the report. |
| TimeRendering | Number of milliseconds spent rendering the report. |
| Source | Source of the report execution. Possible values: Live Cache: indicates a cached execution, for example, dataset queries aren't executed live. Snapshot History AdHoc: Indicates either a dynamically generated report model based drill through report. It can also refer to a Report Builder report that's previewed on a client utilizing the report server for processing and rendering. Session: Indicates a follow-up request within an already established session. For example, the initial request is to view page 1, and the follow-up request is to export to Excel with the current session state. Report Definition Customization Extension (RDCE): Indicates a Report Definition Customization Extension. An RDCE custom extension can dynamically customize a report definition before the definition is passed to the processing engine upon report execution. |
| Status | Status (either rsSuccess or an error code; if multiple errors occur, only the first error is recorded). |
| ByteCount | Size of rendered reports in bytes. |
| RowCount | Number of rows returned from queries. |
| AdditionalInfo | An XML property bag containing additional information about the execution. The contents can be different for each row. |
The AdditionalInfo field
The AdditionalInfo field is an XML property bag or structure containing additional information about the execution. The contents can be different for each row in the log.
The following are examples of the contents of the AddtionalInfo field for both standard and verbose logging:
Standard logging example of AddtionalInfo
<AdditionalInfo>
<ProcessingEngine>2</ProcessingEngine>
<ScalabilityTime>
<Pagination>0</Pagination>
<Processing>0</Processing>
</ScalabilityTime>
<EstimatedMemoryUsageKB>
<Pagination>0</Pagination>
<Processing>6</Processing>
</EstimatedMemoryUsageKB>
<DataExtension>
<SQL>1</SQL>
</DataExtension>
<Connections>
<Connection>
<ConnectionOpenTime>147</ConnectionOpenTime>
<DataSets>
<DataSet>
<Name>DataSet1</Name>
<RowsRead>16</RowsRead>
<TotalTimeDataRetrieval>642</TotalTimeDataRetrieval>
<ExecuteReaderTime>63</ExecuteReaderTime>
</DataSet>
<DataSet>
<Name>DataSet2</Name>
<RowsRead>3</RowsRead>
<TotalTimeDataRetrieval>157</TotalTimeDataRetrieval>
<ExecuteReaderTime>60</ExecuteReaderTime>
</DataSet>
</DataSets>
</Connection>
</Connections>
</AdditionalInfo>
Verbose logging example of AdditionalInfo
<AdditionalInfo>
<ProcessingEngine>2</ProcessingEngine>
<ScalabilityTime>
<Pagination>0</Pagination>
<Processing>0</Processing>
</ScalabilityTime>
<EstimatedMemoryUsageKB>
<Pagination>0</Pagination>
<Processing>6</Processing>
</EstimatedMemoryUsageKB>
<DataExtension>
<SQL>1</SQL>
</DataExtension>
<Connections>
<Connection>
<ConnectionOpenTime>127</ConnectionOpenTime>
<DataSource>
<Name>DataSource1</Name>
<DataExtension>SQL</DataExtension>
</DataSource>
<DataSets>
<DataSet>
<Name>DataSet1</Name>
<RowsRead>16</RowsRead>
<TotalTimeDataRetrieval>655</TotalTimeDataRetrieval>
<QueryPrepareAndExecutionTime>94</QueryPrepareAndExecutionTime>
<ExecuteReaderTime>33</ExecuteReaderTime>
<DataReaderMappingTime>30</DataReaderMappingTime>
<DisposeDataReaderTime>1</DisposeDataReaderTime>
</DataSet>
<DataSet>
<Name>DataSet2</Name>
<RowsRead>3</RowsRead>
<TotalTimeDataRetrieval>16</TotalTimeDataRetrieval>
<QueryPrepareAndExecutionTime>2</QueryPrepareAndExecutionTime>
<ExecuteReaderTime>1</ExecuteReaderTime>
<DataReaderMappingTime>0</DataReaderMappingTime>
<DisposeDataReaderTime>0</DisposeDataReaderTime>
</DataSet>
</DataSets>
</Connection>
</Connections>
</AdditionalInfo>
The following examples are some of the values you see in the AdditionalInfo field:
ProcessingEngine
If most of your reports are still showing the value of 1, you might investigate how to redesign them so that they utilize the newer and more efficient on-demand processing engine.
1=SQL Server 2005, 2=The new On-demand Processing Engine<ProcessingEngine>2</ProcessingEngine>ScalabilityTime
The number of milliseconds spent performing scale related operations in the processing engine. A value of
0indicates that no other time was spent on scale operations and a0also indicates the request isn't under memory pressure.<ScalabilityTime> <Processing>0</Processing> </ScalabilityTime>EstimatedMemoryUsageKB
An estimate of the peak amount of memory, in kilobytes, consumed by each component during a particular request.
<EstimatedMemoryUsageKB> <Processing>38</Processing> </EstimatedMemoryUsageKB>DataExtension
The types of data extensions or data sources used in the report. The number is a count of the number of occurrences of the particular data source.
<DataExtension> <DAX>2</DAX> </DataExtension>ExternalImages
The value is in milliseconds. This data can be used to diagnose performance issues. The time needed to retrieve images from an external web server might slow the overall report execution.
<ExternalImages> <Count>3</Count> <ByteCount>9268</ByteCount> <ResourceFetchTime>9</ResourceFetchTime> </ExternalImages>Connections
A multi-leveled structure
<Connections> <Connection> <ConnectionOpenTime>127</ConnectionOpenTime> <DataSource> <Name>DataSource1</Name> <DataExtension>SQL</DataExtension> </DataSource> <DataSets> <DataSet> <Name>DataSet1</Name> <RowsRead>16</RowsRead> <TotalTimeDataRetrieval>655</TotalTimeDataRetrieval> <QueryPrepareAndExecutionTime>94</QueryPrepareAndExecutionTime> <ExecuteReaderTime>33</ExecuteReaderTime> <DataReaderMappingTime>30</DataReaderMappingTime> <DisposeDataReaderTime>1</DisposeDataReaderTime> </DataSet> <DataSet> <Name>DataSet2</Name> <RowsRead>3</RowsRead> <TotalTimeDataRetrieval>16</TotalTimeDataRetrieval> <QueryPrepareAndExecutionTime>2</QueryPrepareAndExecutionTime> <ExecuteReaderTime>1</ExecuteReaderTime> <DataReaderMappingTime>0</DataReaderMappingTime> <DisposeDataReaderTime>0</DisposeDataReaderTime> </DataSet> </DataSets> </Connection> </Connections>
Log fields (ExecutionLog2)
This view added a few new fields and renamed a few others. The following sample is a Transact SQL statement that retrieves rows from the view ExecutionLog2. The sample assumes the report server database is named ReportServer:
Use ReportServer
select * from ExecutionLog2 order by TimeStart DESC
The following table describes the data that's captured in the report execution log.
| Column | Description |
|---|---|
| InstanceName | Name of the report server instance that handled the request. |
| ReportPath | The path structure to the report. A report saved in the root folder as test, has a ReportPath of /test.A report named test that's saved in the folder Samples, would have a ReportPath of /Samples/test/. |
| UserName | User identifier. |
| ExecutionID | |
| RequestType | Request type is either user or system. |
| Format | Rendering format. |
| Parameters | Parameter values used for a report execution. |
| ReportAction | Possible values: Render, Sort, BookMarkNavigation, DocumentNavigation, GetDocumentMap, Findstring. |
| TimeStart | Start time that indicates the duration of a report process. |
| TimeEnd | End time that indicates the duration of a report process. |
| TimeDataRetrieval | Number of milliseconds spent retrieving the data. |
| TimeProcessing | Number of milliseconds spent processing the report. |
| TimeRendering | Number of milliseconds spent rendering the report. |
| Source | Source of the report execution. Options are 1=Live, 2=Cache, 3=Snapshot, 4=History. |
| Status | Status is either rsSuccess or an error code. If multiple errors occur, only the first error is recorded. |
| ByteCount | Size of rendered reports in bytes. |
| RowCount | Number of rows returned from queries. |
| AdditionalInfo | An XML property bag containing additional information about the execution. |
Log fields (ExecutionLog)
The following sample is a Transact SQL statement that retrieves rows from the view ExecutionLog. The sample assumes the report server database is named ReportServer:
Use ReportServer
select * from ExecutionLog order by TimeStart DESC
The following table describes the data that's captured in the report execution log.
| Column | Description |
|---|---|
| InstanceName | Name of the report server instance that handled the request. |
| ReportID | Report identifier. |
| UserName | User identifier. |
| RequestType | Possible values: True = A Subscription request False= An Interactive request |
| Format | Rendering format. |
| Parameters | Parameter values used for a report execution. |
| TimeStart | Start and stop times that indicate the duration of a report process. |
| TimeEnd | |
| TimeDataRetrieval | Number of milliseconds spent retrieving the data, processing the report, and rendering the report. |
| TimeProcessing | |
| TimeRendering | |
| Source | Source of the report execution. Possible values: (1=Live, 2=Cache, 3=Snapshot, 4=History, 5=Adhoc, 6=Session, 7=RDCE). |
| Status | Possible values are rsSuccess, rsProcessingAborted, or an error code. If multiple errors occur, only the first error is recorded. |
| ByteCount | Size of rendered reports in bytes. |
| RowCount | Number of rows returned from queries. |