SSRS report is VERY SLOW in prod, but SQL query is triggered by FAST

Question

I spent several hours troubleshooting, and I need a fresh perspective.,.

We have a relatively simple report setup in SSRS, a simple matrix with columns at the top and data points going down. The SQL query for the report is of "medium" complexity - it has some subqueries and several joins, but nothing crazy.

The report worked perfectly for several months and has recently become REALLY slow. For example, 15-20 minutes to create a report. I can pin and paste the SQL query from the report designer in SQL Mgmt Studio, replace the necessary variables, and it scrolls the results in less than 2 seconds. I even went so far as to use the SQL profiler to get the exact query that SSRS executes, and cut and paste it into Mgmt Studio, still the same, the results for the second. The parameters and date ranges do not matter, I can set the parameters to return a small data array (<100 rows) or large (> 10,000 rows) and all the same results; super-fast at Mgmt Studio, but 20 minutes to create an SSRS report.

Troubleshooting I have tried so far: Deleted and redeployed the report in SSRS. Tested in Visual Studio IDE on several computers and on the SSRS server, at the same speed (~ 20 minutes) in both places. The SQL Profiler used to monitor the SPID executing the report captured all the SQL queries that were executed and tried them individually (and together) in Mgmt Studio - quickly starts in Mgmt Studio (<2 seconds) Monitoring server performance during report execution. The processor is pretty damned for the 20-minute report generation, disk I / O is slightly higher than the base

Answer 1

Check the execution plans for both to make sure that the combination of sniffing parameters and / or differences in set_options did not create two separate execution plans.

This is a script that I met while executing a request from ADO.Net and SSMS. The problem arose when using different options created different execution plans. SQL Server uses the parameter value passed to try to further optimize the generated execution plan. I found that for each of the generated execution plans, different parameter values ​​were used, which led to an optimal and suboptimal plan. I cannot find my initial queries to verify this at the moment, but a quick search reveals this article related to the same issue.

http://www.sqlservercentral.com/blogs/sqlservernotesfromthefield/2011/10/25/multiple-query-plans-for-the-same-query_3F00_/

If you are using SQL Server 2008, there is also an alternative provided through a query hint called "OPTIMIZE FOR UNKNOWN", which essentially disables the sniffing option. Below is a link to an article that helped me in the original research on this feature.

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

An alternative to the foregoing for versions earlier than 2008 was to store the parameter value in a local variable as part of the procedure. This will behave just like the tip above. This review is based on the following article (as amended).

---

Edit

In a more detailed search, an article was found with a very in-depth analysis of the subject, if used, the link below.

http://www.sommarskog.se/query-plan-mysteries.html

Answer 2

This problem was a problem for us. We run SSRS reports from CRM 2011. I tried a number of suggested solutions (mapping input parameters to local variables, adding WITH RECOMPILE to the stored procedure) without any luck.

This article describes the memory configuration of the report server ( http://technet.microsoft.com/en-us/library/ms159206.aspx ), in particular adding the 4000000 value to our RSReportServer.config problem.

Reports that would take 30-60 seconds to render are now completed in less than 5 seconds, which is about the same time that the main stored procedure is taken for execution in SSMS.