14

We have this problem in our production environment.

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) - Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1).

SQL Server is dropping all (almost 100% of) the old execution plans and recreating them everyday overnight (from 11:00 PM to 8:00 AM). This was even happening when the 'auto update stats' was at disabled state. We've turned the 'auto update stats' on for the last 2-3 weeks. But it's still happening.

We don’t really know what triggers this re-generation of plans but we are sure we don’t do it manually.

The only thing that really coincides with the timing of the plans being regenerated is a DB maintenance job that we have: the daily index reorganizing (when fragmentation is 5-30%), and the daily index rebuilding (when fragmentation is more than 30%) job. Usually this daily maintenance job only does reorganizing (as the index fragmentation is never more than 30% on daily basis).

Impact:

These newly created plans make some UDF calls/query calls (that are called from UI/web pages) take way longer (minutes as opposed to less than 1 second), and so the sessions just get piled up taking the CPU close to 90%.

The problem goes away the moment those stuck sessions are forcefully deleted (on the DB side), and 1) when all the corresponding execution plans are cleared manually (for queries) or 2) when the UDFs are altered (for functions). Any new plans created by SQL server from that moment work just perfect throughout the day until it ends up having the same issue next morning. Also, this behavior is not 100% consistent, we're not really seeing it each and every morning. But there have been periods of time where we've been seeing it consistently for 4-5 days in a row.

The problem happens on business mornings, that's when UI/web pages are accessed more intensively, it seems.

Does anyone have a clue what is causing this and how to solve this problem? Any help would be much appreciated.

Improve this question
13
  • 3
    the plancache can be released either when the machine is under memory pressure or if you change settings ob db level. (alter db). Since you said you don't delete them "manually" I assume it might be memory pressure. How much memory does the machine have? whats your max memory settings? do you have a virtual environment and maybe overallocated RAM?
    – RayofCommand
    Commented May 18, 2015 at 15:13
  • 6
    Why are you at SP1. Before you do any thing apply SP3. SQL Server can force plans out if it finds memory pressure and need to more memory to accommodate pages specially from index rebuild specially if you have big tables. Index rebuild would try to bring as much page as possible. What you can do is stop using MP and use Ola Hallengren solution and see if this helps. What is max server memory ?
    – Shanky
    Commented May 18, 2015 at 16:00
  • 1
    Guys, I am not a DBA, only a SQL developer. I am just asking all this as it's been going on for quite some time. Thanks for your comments, I will try to respond to all of them, even though for now I find it hard to follow (and it all seems pretty obvious for you). What is MP?
    – peter.petrov
    Commented May 18, 2015 at 16:05
  • 1
    @peter.petrov we are trying to help you by getting to know your environment. MP = Maintenance Plans.
    – Kin Shah
    Commented May 18, 2015 at 16:46
  • 1
    The real problem is that your query plans are so fragile. Recompilations can happen at any time, even during the day. No guarantees. Fix your queries so that the plans become stable. OPTION RECOMPILE or OPTIMIZE FOR UNKNOWN are sledgehammer approaches that can be appropriate and be a quick fix.
    – usr
    Commented May 18, 2015 at 23:01

1 Answer 1

Reset to default
2

Well I have some ideas which could cause this behavior.

  1. Do you monitor your memory pressure? Maybe your queries raise a certain limit which will cause the flush of the plan cache. I don't know your application, but does this correspondent with your logs from your frontend servers? Is there pressure too during this time?
  2. Do you have a dedicated SQL Server or does the server share it's hardware with other processes/services? If not, try to consider to outsource your SQL Server to a dedicated machine instead. This will reduce side effects from other services.
  3. You may want to use optimize for ad hoc workloads, which will just save a plan stub and compile it if it's needed. This will reduce the load of your plancache, which will lower the chance of an plancache flushing. You can enable it using sp_configure 'optimize for ad hoc workloads',1; reconfigure. This can be done if you have enabled the advanced options using sp_configure 'show advanced options',1; reconfigure.
  4. Another idea can be backups. Just simple backups. If they are aggressively, it may occur that your machine gets under pressure too. The time your mentioning just sounds like a good timespan for planning a backup.
  5. Maybe it's quite simple a bug in your maintenance script. Have you checked if there is a logical issue which causes your script to rebuild all indices instead of just those who matches the criteria. This maybe can cause it too.

Just beside all of this possibilities, it may be useful to check the log files for some changes to the options affinity mask, affinity I/O mask and their x64 partners. Another thing can be a change of the MAXDOP option of your instance. Please check the logs for them too. They will need to flush the plancache too.

Last but not least, you can still run a serverside trace (just setting it up using the profiler, start it, stop it and using sql command to start it again on the serverside). Beside that perfmon is your friend. It can watch and monitor your performance values for a time. Maybe you can see parallels in the pressure with certain actions on your server which may cause those flush.

Hopefully this will help you, even if the answer come a bit later.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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