In order to monitor the response times of the reports executed in Reporting Services, you need to understand how Reporting Services works concerning performance. You should know that there are 3 phases in the execution or a report: “data retrieval” (time it takes to retrieve the data from the server using a queries or stored procedures), “processing” (time it takes to process the data within the report using the operations in the layout of the report) and “rendering” (time it takes to display the information of the report and how it is displayed like excel, pdf, html formats…).
In order to be able to know in which of these phases the time is been spent (during the report execution), there is a table in Reporting Services called “ExecutionLog”. This table records every report execution (one per record). The fields “TimeDataRetrieval” (amount of data been retrieved in the dataset), “TimeProcessing” (time to process the data and the operations performed on them), and “TimeRendering” (time it takes to covert the report to the final format for the user like excel, pdf…) show the time in milliseconds for each corresponding phase.
· If the “Data Retrieval” time is high, you could try to decrease the amount of records retrieved in the query or Stored Procedure (you might not need all the data, then you can filter it) or check if the network or devices used is slow when the server is remote…
· If the “Processing” time is high we could try to simplify the operations contained in the layout of the report (grouping, filtering, aggregated operations, post aggregated, custom added code... For example a Matrix would take longer to run than a table).
· If the “Rendering”, time is high, we should consider using lighter formats. For example Excel format is one of the heaviest and html format is lighter.
Reporting Services Support Engineer