SSRS report VERY SLOW in prod but SQL query runs FAST

Question

I've spent hours troubleshooting this and I need some fresh perspective . . .

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

Report has worked fine for months and recently has become REALLY slow. Like, 15-20 minutes to generate the report. I can clip-and-paste the SQL query from the Report Designer into SQL Mgmt Studio, replace the necessary variables, and it ruturns results in less than 2 seconds. I even went so far as to use SQL profiler to get the exact query that SSRS is executing, and clipped-and-pasted this into Mgmt Studio, still the same thing, sub-second results. The parameters and date ranges specified don't make any difference, I can set parameters to return a small dataset (< 100 rows) or a humongous one (> 10,000 rows) and still the same results; super-fast in Mgmt Studio but 20 minutes to generate the SSRS report.

Troubleshooting I've attempted so far: Deleted and re-deployed the report in SSRS. Tested in Visual Studio IDE on multiple machines and on the SSRS server, same speed (~20 minutes) both places Used SQL Profiler to monitor the SPID executing the report, captured all SQL statements being executed, and tried them individualy (and together) in Mgmt Studio -- runs fast in Mgmt Studio (< 2 seconds) Monitored server performance during report execution. Processor is pretty darn hammered during the 20 minute report generation, disk I/O is slightly above baseline

Answer 1

Check the execution plans for both to ensure that a combination of parameter sniffing and/or differences in set_options haven't generated two separate execution plans.

This is a scenario I've come across when executing a query from ADO.Net and from SSMS. The problem occurred when the use of different options created different execution plans. SQL Server makes use of the parameter value passed in to attempt to further optimise the execution plan generated. I found that different parameter values were used for each of the generated execution plans, resulting in both an optimal and sub-optimal plan. I can't find my original queries for checking this at the moment but a quick search reveals this article relating to the same issue.

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

If you're using SQL Server 2008 there's also an alternative provided via query hint called "OPTIMIZE FOR UNKNOWN" which essentially disables parameter sniffing. Below is a link to an article that assisted my original research into 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 above for versions earlier than 2008 would be to store the parameter value in a local variable within the procedure. This would behave in the same way as the query hint above. This tip comes from the article below (in the edit).

---

Edit

A little more searching has unearthed an article with a very in-depth analysis of the subject in case it's of any use, link below.

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