SQL Server 2008 Slow Reporting Reporting Services

Question

I have a problem with SQL Server 2008 Reporting Services. The problem is that the report is sometimes too slow to render (it takes more than 30 minutes), although I took the query and ran it in SQL Server Management Studio, and it took no more than 25 seconds.

The query returns a large table (about 5000 rows), and I use it to draw a pie chart in the report, I tried to optimize the query so that it returns only 4 rows, but the report was slow again.

What bothers me is that sometimes the report (with different entries) runs as fast as the query (about 30 seconds), I thought it might be due to the small number of users, so I tried with some colleagues look at it at the same time, but the reports are still quick, I tried to change the configuration, but I was out of luck.

I have been looking for a solution to this problem for more than two months, so if anyone can help me with this, I will be very grateful.

Answer 1

If you have access to the SQL Server ReportServer database, run the following query or the like in the ExecutionLog view:

select TimeStart, TimeEnd, TimeDataRetrieval, TimeProcessing, TimeRendering, Status, ReportID from the executable

This will provide you with a good breakdown of the rendering of the report (with various parameters). Pay particular attention to TimeRendering, TimeProcessing, and TimeDataRetrieval. Large or high values ​​for any of these columns illustrate where your bottleneck is.

One of the problems that I experienced in the past is when you return a fairly large dataset to the report (5000 rows are large enough for this scenario) and then you use the built-in ssrs filtering, the rendering is very slow and this will result in a very high TimeRendering value . All rendering should be done at the database level, grouping and filtering will not work well if large amounts of data will be performed in the ssrs report itself.