SQL Server Stored procedures run very slowly after a while

Question

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.

Answer 1

What you're describing sounds like parameter sniffing to me. Parameter sniffing occurs because SQL Server caches the execution plan of the procedure if the plan is not found in the cache. Subsequent executions will use that version of the plan. In most cases, that's desirable since it eliminates compilation time. However, if the characteristics of the data accessed by the procedure's queries varies significantly when different parameters are passed, the result can be very poor performance.

Your description of the runtimes of short date ranges versus long ranges fits that pattern perfectly. I would guess that the query optimizer is choosing different indexes in the two scenarios.

You're on the right track for your solutions. WITH RECOMPILE can resolve the parameter sniffing in some cases, but recompilation is a high cost to pay on every execution. OPTION(RECOMPILE) on individual queries can definitely help and is sometimes the best answer, but again, it comes at a high cost. OPTION(OPTIMIZE FOR UNKNOWN) is another possibility, but it resolves the issue by providing a plan that's consistent (but may still be horribly inefficient).

Start by reviewing these articles, which explain parameter sniffing far better than I can in a short answer:

• Kimberly Tripp, Building High Performance Stored Procedures
• Kendra Little, Optimize for… Mediocre?

Then, check the execution plans for your procedure, looking for the specific queries that vary the most between executions. Try OPTION(RECOMPILE), OPTION(OPTIMIZE FOR UNKNOWN), or Kimberly's sp_executesql method on those, rather than the entire procedure.