To analyze the performance of your SSRS report (RDL) you need to understand what will have impact on the total time to generate the reports. The total time to generate a reporting server report (RDL) can be divided into 3 elements:
- Time to retrieve the data (TimeDataRetrieval).
- Time to process the report (TimeProcessing)
- Time to render the report (TimeRendering)
As of SQL Server 2008 R2, this 3 performance components are logged every time for which a deployed report is executed. This information can be found in the table Executionlog3 in the ReportServer database. You can use next query:
SELECT TOP 10 Itempath,parameters,
TimeDataRetrieval + TimeProcessing + TimeRendering as [total time],
TimeDataRetrieval, TimeProcessing, TimeRendering,
ByteCount, [RowCount],Source, AdditionalInfo
ORDER BY Timestart DESC
This is the location and name of the executed report (RDL)
The parameter values used to execute the report.
3. TimeDataRetrievalThe 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
In this example I have a server with multiple CPU's in it. Every dataset is retrieved using a seperate database connection. (See SPID 61, 63,64,65,66,67,68). Execution these datasets over multiple database connections saves a lot of time. TimeDataRetrieval contains the duration of the longest DataSet
4. TimeProcessingThe 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)
5. TimeRenderingThe 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 of SQL server, where TimeProcessing included all expression evaluation.
- PaginationScalabilityTime (see AdditionalInfo column)
- RenderingScalabilityTime (see AdditionalInfo column)
Total number of bytes received from all datasets in the report.
Total number of records received from all datasets in the report.
8. SourceSpecifies the type of the execution. It is one of the following values: Live, Cache, Snapshot, History, AdHoc, Session, Rdce
- Live indicates a live executed dataset queries.
- 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.
- High TimeDataRetrieval.
- Remove not used datasets from your report. Every dataset will be executed, even if it is not used to display data.
- Use SQL Profiler to analyze the data set query to see where you can improve your data set. See Guidelines to write well performing queries.
- Check if the RowCount (number of records returned by all datasets) is not too big. Ask yourself is all data needed in the report. Data which is not needed should not be retrieved.
- Data set will be executed once. You can use one data set for multiple report parts like tablix, chart, lists, sprakline, indicator, data bar, map, gauge etc. Look if you can combine two or more data sets which are almost identical to one dataset.
- Processing the data is done sequentially. The more report parts like tablix, chart, lists, sprakline, indicator, data bar, map, gauge etc. you put on your report, the more time it will take to process the report.
- Minimize the number of records of your data set to process. Do you need all data, or can you group and sort the data in your dataset. This will lower the result set which will results in faster processing time. In most situations the SQL Server engine will group data much faster in comparions with Reporting Services.
- Be critical if all rendering is needed.