More tips to improve performance of SSRS reports.

In the past I have written something about performance in combination with SQL Server Reporting Services.See  Analyze performance of your reporting services reports by using SSRS statistics and Tips to improve performance of MS Reporting service reports (SSRS). In this blog post I will give some more tips to analyze and improve the performance of your SQL 2008 R2 SSRS reports.

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:

1. Time to retrieve the data (TimeDataRetrieval).
2. Time to process the report (TimeProcessing)
3. Time to render the report (TimeRendering)  

Total time = (TimeDataRetrieval) + (TimeProcessing) + (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
FROM ExecutionLog3
ORDER BY Timestart DESC

1. Itempath
This is the location and name of the executed report (RDL)

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

TimeDataRetrieval is is the time needed for SQL Server to retrieve the data of all datasets in your report. This is time spent openings connections to the data source and time spent reading data rows from the data extension. Keep in mind that all dataset defined in your report will be retrieved. Even if you do not use it in the report to display the data. Remove datasets which are not used to display data. Datasets will be executed in parallel, by making use of multiple database connections. In next example you will that my report is started at 2011-06-29 14:16:42.677 and ended at 2011-06-29 14:16:46.757. Total time to retrieve the data = 4 seconds and 80 milliseconds.  However every the total time of every single data set is much more: 235 + 1242 + 2442 + 3470 + 3678 + 4069 = 15 seconds and 136 milliseconds.

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)

Processing time can be very high if you have datasets with a big number of records for which a lot of GROUP BY and SORTING need to be done.

 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)

 6. ByteCount
Total number of bytes received from all datasets in the report.

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

The information in the Executionlog3 table will help you to find the bottlenck in your report. What to do in the situation of:

1. 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. 
2. High Timeprocessing.
• 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. 
3. High TimeRendering.
• Be critical if all rendering is needed.

Enjoy it, to deliver a great user experience to your users by improving the performance of your reports.