3

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.

  • Is CrucialTable very volatile with many deletions and insertions? If the table is also quite large it can take a while before the table gets automatically recompiled. Try scheduling more frequent statistics updates. – RLF Apr 28 '15 at 3:02
  • 3
    Also try OPTION (RECOMPILE) on individual statements instead of WITH RECOMPILE on the procedures. – Aaron Bertrand Apr 28 '15 at 3:03
  • 4
    If you want more specific, you'll need to focus. Pick a single query that slows down and show us the table/index structure, the query, and the actual, post-execution plans from both when it's slow and when it's not. – Aaron Bertrand Apr 28 '15 at 3:06
  • @RLF No the table isn't that volatile. It's actually a table of customer orders which then has relationships to order items, payments and deliveries (all separate tables), so basically it has a steady addition of rows each day, but small in comparison to the overall number of rows. – BenC3 Apr 28 '15 at 5:44
  • @AaronBertrand thanks for the info. It's been a bit tricky to narrow down the problem query as it's only happening in production, but we plan to simulate the data in dev and see if we can reproduce. – BenC3 Apr 28 '15 at 5:46
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:

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.

  • Thanks for the detailed response, I'm inclined to agree with you on the cause, but I'm getting some strange behaviour now even when using the recompile options. I've updated the question with more info. – BenC3 Apr 29 '15 at 5:15
  • @BenC3 Have you checked the statistics on the tables referenced by the procedure to see if they may be out of date? That can also contribute heavily to parameter sniffing. DBCC SHOW_STATISTICS can be used to view the statistics on a table, and UPDATE STATISTICS can be used to update them. – Ed Leighton-Dick Apr 29 '15 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.