May 18, 2015 at 9:02 am
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 <X64> (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.
May 18, 2015 at 9:07 am
p.a.petrov (5/18/2015)
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).
And right there is your cause.
Rebuilding indexes on a table will update statistics which will invalidate all plans which use the table. Reorganising shouldn't invalidate plans afaik, but I don't know if there's other stuff in that maintenance plan or if it's causing memory pressure resulting in a trim of the plan cache.
In the long term I would suggest that you look at replacing the UDFs with optimal code. User-defined functions seldom perform well
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply