TechNet
Export (0) Print
Expand All

Report Server ExecutionLog and the ExecutionLog3 View

 

Applies To: SQL Server 2016

The Reporting Services, report server execution log contains information about the reports that execute on the server or on multiple servers in a native mode scale-out deployment or a SharePoint farm. You can use the report execution log to find out how often a report is requested, what output formats are used the most, and how many milliseconds of processing time is spent on each processing phase. The log contains information on the length of time spent executing a report's dataset query and the time spent processing the data. If you are a report server administrator, you can review the log information and identify long running tasks and make suggestions to the report authors on the areas of the report (dataset or processing) they may be able to improve.

Report servers configured for SharePoint mode, can also utilize the SharePoint ULS logs. For more information, see Turn on Reporting Services events for the SharePoint trace log (ULS)

The report server execution 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 are not impacted. If you do not have a dependence on an older view, for example ExecutionLog, it is recommended you use the most recent view, ExecutionLog3.

In this topic:

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 will be available at any given time.

You cannot set limits on the number of rows or on the type of entries that are logged.

To enable execution logging:

  1. From SharePoint Central Administration, click Manage service applications in the Application Management group.

  2. Click the name of the Reporting Services service application you want to configure.

  3. Click System Settings.

  4. Select Enable Execution Logging in the Logging section.

  5. Click OK.

To enable verbose logging:

You need to enable logging as described in the previous steps and then complete the following:

  1. From the System Settings page of your Reporting Services services application, find the User-defined section.

  2. Change the ExecutionLogLevel to verbose. This field is a text entry field and the two possible values are verbose and normal.

You can turn report execution logging on or off from the Server Properties page in SQL Server Management Studio. The EnableExecutionLogging is and 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 will be available at any given time.

You cannot set limits on the number of rows or on the type of entries that are logged.

To enable execution logging:

  1. Start SQL Server Management Studio with administrative privileges. For example right-click the Management Studio icon and click ‘Run as administrator’.

  2. Connect to the desired report server.

  3. Right-click the server name and click Properties. If the Properties option is disabled, verify you ran SQL Server Management Studio with administrative privileges.

  4. Click the Logging page.

  5. Select Enable report execution Logging.

To enable verbose logging:

You need to enable logging as described in the previous steps and then complete the following:

  1. From the Server Properties dialog, click the Advanced page.

  2. 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.

This view added additional performance diagnostics node inside the XML based AdditionalInfo column. The AdditionalInfo column contains an XML structure of 1 to many additional fields of information. The following is a sample Transact SQL statement to retrieve 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 is captured in the report execution log

ColumnDescription
InstanceNameName 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.
ItemPathPath of where a report or report item is stored.
UserNameUser identifier.
ExecutionIDThe internal identifier associated with a request. Requests on the same user sessions share the same execution id.
RequestTypePossible Values:

Interactive

Subscription

 

Analyzing log data filtered by RequestType=Subscription and sorted by TimeStart may reveal periods of heavy subscription usage and you may want to modify some of the report subscriptions to a different time.
FormatRendering format.
ParametersParameter values used for a report execution.
ItemActionPossible values:

Render

Sort

BookMarkNavigation

DocumentNavigation

GetDocumentMap

Findstring

Execute

RenderEdit
TimeStartStart and stop times that indicate the duration of a report process.
TimeEnd
TimeDataRetrievalNumber of milliseconds spent retrieving the data.
TimeProcessingNumber of milliseconds spent processing the report.
TimeRenderingNumber of milliseconds spent rendering the report.
SourceSource of the report execution. Possible values:

Live

Cache: indicates a cached execution, for example, dataset queries are not executed live.

Snapshot

History

AdHoc: Indicates either a dynamically generated report model based drill through report, or a Report Builder report that is 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.

Rdce: Indicates a Report Definition Customization Extension. An RDCE custom extension can dynamically customize a report definition before it is passed to the processing engine upon report execution.
StatusStatus (either rsSuccess or an error code; if multiple errors occur, only the first error is recorded).
ByteCountSize of rendered reports in bytes.
RowCountNumber of rows returned from queries.
AdditionalInfoAn XML property bag containing additional information about the execution. The contents can be different for each row.

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 are some of the values you will see in the AdditionalInfo field:

  • ProcessingEngine

    1=SQL Server 2005, 2=The new On-demand Processing Engine. If a majority of your reports are still showing the value of 1, you may investigate how to redesign them so they utilize the newer and more efficient on-demand processing engine.

    <ProcessingEngine>2</ProcessingEngine>

  • ScalabilityTime

    The number of milliseconds spent performing scale related operations in the processing engine. A value of 0 indicates that no additional time was spent on scale operations and a 0 also indicates the request was not 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

    Added in SQL Server 2012

    The value is in miliseconds. This data can be used to diagnose performance issues. The time needed to retrieve images from an external webserver may slow the overall report execution.

    <ExternalImages>  
        <Count>3</Count>  
        <ByteCount>9268</ByteCount>  
        <ResourceFetchTime>9</ResourceFetchTime>  
    </ExternalImages>  
    
    
  • Connections

    Added in SQL Server 2012

    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>  
    
    
    

This view added a few new fields and renamed a few others. The following is a sample Transact SQL statement to retrieve 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 is captured in the report execution log

ColumnDescription
InstanceNameName of the report server instance that handled the request.
ReportPathThe path structure to the report. For example a report named ”test” which is the in root folder in Report Manager, would have a ReportPath of “/test”.

A report named “test” that is saved in the folder “samples” on Report Manager , will have a ReportPath of “/Samples/test/”
UserNameUser identifier.
ExecutionID
RequestTypeRequest type (either user or system).
FormatRendering format.
ParametersParameter values used for a report execution.
ReportActionPossible values: Render, Sort, BookMarkNavigation, DocumentNavigation, GetDocumentMap, Findstring
TimeStartStart and stop times that indicate the duration of a report process.
TimeEnd
TimeDataRetrievalNumber of milliseconds spent retrieving the data, processing the report, and rendering the report.
TimeProcessing
TimeRendering
SourceSource of the report execution (1=Live, 2=Cache, 3=Snapshot, 4=History).
StatusStatus (either rsSuccess or an error code; if multiple errors occur, only the first error is recorded).
ByteCountSize of rendered reports in bytes.
RowCountNumber of rows returned from queries.
AdditionalInfoAn XML property bag containing additional information about the execution.

The following is a sample Transact SQL statement to retrieve 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 is captured in the report execution log

ColumnDescription
InstanceNameName of the report server instance that handled the request.
ReportIDReport identifier.
UserNameUser identifier.
RequestTypePossible values:

True = A Subscription request

False= An Interactive request
FormatRendering format.
ParametersParameter values used for a report execution.
TimeStartStart and stop times that indicate the duration of a report process.
TimeEnd
TimeDataRetrievalNumber of milliseconds spent retrieving the data, processing the report, and rendering the report.
TimeProcessing
TimeRendering
SourceSource of the report execution. Possible values: (1=Live, 2=Cache, 3=Snapshot, 4=History, 5=Adhoc, 6=Session, 7=RDCE).
StatusPossible values: rsSuccess, rsProcessingAborted, or an error code. If multiple errors occur, only the first error is recorded.
ByteCountSize of rendered reports in bytes.
RowCountNumber of rows returned from queries.

Turn on Reporting Services events for the SharePoint trace log (ULS)
Reporting Services Log Files and Sources
Errors and Events Reference (Reporting Services)

Community Additions

ADD
Show:
© 2016 Microsoft