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

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

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

3. Select System Settings.

4. Select Enable Execution Logging in the Logging section.

5. 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:

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.

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

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

2. Connect to the desired report server.

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

4. Select the Logging page.

5. 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:

1. From the Server Properties dialog, select 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.

   

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.

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 AdditionalInfo field for both standard and verbose logging:

Standard 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>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 0 indicates that no other time was spent on scale operations and a 0 also 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.

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.

Related content

• Reporting Services log files and sources
• Errors and events reference (Reporting Services)