Portal
Language
 
Home>Knowledge Base>SCRUBS>Understanding SQL Reporting Services Performance Data
Information
Article ID8
Created On11/9/2009
Modified11/9/2009
Understanding SQL Reporting Services Performance Data
SQL Server Reporting Services 2008 introduces the ExecutionLog2 view, and with it, many key metrics. These are used as the basis for SCRUBS for SQL Server 2008. The following field definitions are from MSDN.

ExecutionLog2 Columns

  • InstanceName
    The RS instance (machine or RS Instance db) which processed the request.  In a scale-out configuration, this value specifies which node/instance of the RS web farm executed a particular request.  The InstanceName distribution may help determine whether your network-load balancer distributes requests across a RS scale-out configuration as expected.
     
  • ReportPath
    Specifies the full path and name of the report executed.  If the ReportPath is null, it is an ad-hoc execution (Source=Adhoc) meaning that it is either a dynamically generated report model based drillthrough report, or a Report Builder 2.0 report that is previewed on a client utilizing the report server for processing and rendering.
     
  • UserName
    Specifies the user who initiated the request.  For subscription executions, this is the RS service account.
     
  • ExecutionId
    The id associated with the request. 
     
  • RequestType
    Specifies the type of the request: { Interactive, Subscription }
     
  • Format
    The rendering format for a particular request.  If null, then the request is an interactivity event (such as toggle, drillthrough, interactive sort) and not a rendering request.  Typical values for rendering formats in RS 2008 include RPL, HTML4.0, Excel, Word, PDF.
    RPL (Report Page Layout Format) in RS 2008 represents interactive viewing of a report in HTML (report server / report manager) or Preview in Report Builder 2.0 when connected to a report server. 
    HTML4.0 RS 2008 means that the report was not requested with the new viewer controls, but likely with the VS 2005 or VS 2008 report viewer controls in server mode that don't support client side rendering yet.  This capability will only be available with the updated report viewer controls.
     
  • Parameters
    The report parameter values associated with the request.
     
  • ReportAction
    The action performed on the report.  It is one of the following values: { Render, BookmarkNavigation, DocumentMapNavigation, DrillThrough, FindString, GetDocumentMap, Toggle, Sort }
     
  • TimeStart
    Time the request began.
     
  • TimeEnd
    Time the request finished.
      
  • TimeDataRetrieval
    The number of milliseconds spent interacting with data sources and data extensions for all data sets in the main report and all of its subreports. This value includes:
    - Time spent opening connections to the data source
    - Time spent reading data rows from the data extension
    Note: If a report has multiple data sources/data sets that can be executed in parallel, TimeDataRetrieval contains the duration of the longest DataSet, not the sum of all DataSets durations.  If DataSets are executed sequentially, TimeDataRetrieval contains the sum of all DataSet durations.
     
  • TimeProcessing
    The number of milliseconds spent in the processing engine for the request.  This value includes:
    - Report processing bootstrap time
    - Tablix processing time (e.g. grouping, sorting, filtering, aggregations, subreport processing), but excludes on-demand expression evaluations (e.g. TextBox.Value, Style.*)
    - ProcessingScalabilityTime (see AdditionalInfo column)
     
  • TimeRendering
    The number of milliseconds spent after the Rendering Object Model is exposed to the rendering extension.  This value includes:
    - Time spent in renderer
    - Time spent in pagination modules
    - Time spent in on-demand expression evaluations (e.g. TextBox.Value, Style.*). This is different from prior releases, where TimeProcessing included all expression evaluation. 
    - PaginationScalabilityTime (see AdditionalInfo column)

     Important Notes on TimeDataRetrieval, TimeProcessing, TimeRendering
    A value of -1 for any of the timing related columns indicates that the report server detected that the timer was unreliable.  This can happen on particular multi-proc/core hardware, particularly x64, when the CPU clocks get out of sync.
    Execution log entries are created only for the initial request to the server. 
  • Source
    Specifies the type of the execution.  It is one of the following values: { Live, Cache, Snapshot, History, AdHoc, Session, Rdce }
    Cache indicates a cached execution, i.e. dataset queries are not executed live.
    AdHoc indicates either a dynamically generated report model based drillthrough report, or a Report Builder 2.0 report that is previewed on a client utilizing the report server for processing and rendering.
    Session indicates a subsequent request within an already established session (e.g. the initial request is to view page 1, and the subsequent request is to export to Excel with the current session state). 
    Rdce indicates a Report Definition Customization Extension (RDCE; a new feature in RS 2008) that can dynamically customize a report definition before it is passed to the processing engine upon report execution.  
      
  • Status
    rsSuccess if the execution succeeded. 
    If multiple errors occurred, only the first critical error code is recorded.
     
  • ByteCount
    Number of bytes generated as output streams for this request.
     
  • RowCount
    Number of data set rows processed in this request.
     
  • AdditionalInfo
    An XML set that contains additional performance metrics.
  • AdditionalInfo Properties

  • ProcessingEngine
    Identifies the version of the processing engine utilized for the report. 
    1 = Yukon Engine
    2 = On-Demand Processing Engine
    After a deployment is upgraded to RS 2008, reports will internally auto-upgrade to the latest RDL 2008 schema so that the on-demand procesing engine can be used for processing.  However, if the auto-upgrade is not possible (e.g. reports that were built with RS 2003 CustomReportItem controls, those particular reports will be processed with the old Yukon engine in a transparent backwards-compatibility mode.
     
  • ScalabilityTime
    A value of 0 indicates that no additional time was spent doing scalability operations and means the request was not under memory pressure.  If the value is > 0, it represents the cumulative number of milliseconds spent performing scalability specific operations in a particular report server component to react to memory pressure during the request. 
    A scalability time value should be set in perspective with the total execution time (TimeEnd - TimeStart) of the request.  If a report frequently shows high relative scalability time values, it is a great starting point for optimizing that particular report to reduce memory pressure and optimize the overall system as well.
     
  • EstimatedMemoryUsageKB
    In short, this indicates resource utilization of a report.
    A value of 0 indicates memory usage too low to be tracked specifically to minimize the overhead of this algorithm.  If the value is > 0, it represents an estimate of the peak amount of memory, in kilobytes, consumed by a particular component during a request.  These memory estimates only include objects allocated via the report server's scalability infrastructure, which is mainly utilized by data size dependent operations such as grouping, sorting, filtering, aggregations, page layout, and rendering operations.  Generally, reports with estimated peak memory usage above 10000 (i.e. above 10 MB) for scalable data structures may deserve a closer look from a report design point of view.
     
  • DataExtension
    Identifies which types of data extensions / data sources are utilized by the report.  For report models, this is prefixed with "Model:". The numeric value of the element is a counter to determine how many times that particular type of data source is queried.
  •