Introduction

Sometimes when dealing with reports and you find your report is slow , you need know why he consumes this time, in this post i will show to you how to measure the performance of you SSRS Report.

SSRS Performance  Measurement 

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

The report server logs data about report execution into an internal database table. The information from the table is available from SQL views.

The report execution log is stored in the report server database that by default is named ReportServer. The following 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.

For more Information :

https://msdn.microsoft.com/en-us/library/ms159110.aspx

Also you Configuration Settings for the Report Server Execution Log

You can turn report execution logging on or off from the Server Properties page in SQL Server Management Studio.
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 can use next query:

SELECT TOP 1 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. 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 sub reports. 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. 

4. TimeProcessing

 The number of milliseconds spent in the processing engine for the request. This value includes:

• 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. 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 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. Source

Specifies 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 Execution Plane to analyze your query if it needs some of (indexes or some of wrong Joins that retrieves many data not needed in your report ) 
• 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.
2. High Time processing.
• 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 Time Rendering.

• Be critical if all rendering is needed.