We have an ASP.Net application with a report page that runs several stored procedures. We have a problem with a production system where the report runs so slowly that it times out when the user selects a longer date range.
When the slow down occurs, we are able to restore functionality by running EXEC sp_recompile 'CrucialTable'
on a table that is touched by all of the stored procedures, and then immediately running the report for a long date range. This fixes the problem for a while, but it slowly creeps back.
My guess was a bad execution plan so I added WITH RECOMPILE
to the stored procedures, but sadly it didn't fix the problem.
We are using SQL Azure if that makes any difference.
UPDATE
I discovered a couple of SPs that were still missing the recompile options so that has now been added and now we have a new problem. The long date range reports run great (under 4 seconds which is fine given the amount of crunching that is taking place), but oddly now the short range reports are running horribly. For example a report that covers 2 years worth of transactions takes about 3.5 seconds, but 2 weeks takes 20 seconds!
UPDATE 2
I've managed to replicate the problem on a dev machine and have a look at the execution plan. I've tried a raft of option combinations like OPTION(RECOMPILE)
and OPTIMIZE
but as it turns out the query itself runs quite fast.
The problem is that
The query had to wait xx seconds for MemoryGrant during execution
xx ranged from 20 to 90 seconds depending on the run.
That struck me as a but unusual because there were no other queries running at the time and the dev machine has plenty of ram. The execution plan also suggested a missing index so I'm going to try and add that and see if it helps.
UPDATE 3
I've experimented with a number of recompile/optimize options but the solution still eludes me.
Basically the stored procedures all have recompile options on them now, and in test everything works great even with big data (queries take less than a second). My initial problem of MemoryGrant has stopped, which I think was just because at the time the machine it was running on had a lot of applications open and was a little low on free ram.
In production we now have all SPs with the same recompile options on them, but unfortunately the short range queries continue to be incredibly slow. I've run the same SPs using SSMS to the production DB (with ARITHABORT set to OFF to simulate a similar connection) and they run virtually instantaneously regardless of a long or short range.
When running the short range report from the application in production there is an entry in the sys.dm_exec_query_memory_grants
table for about 12 seconds. The full report then takes another 14+ seconds to complete after that entry has disappeared.