February 11, 2013 at 11:22 am
Hey guys,
I was looking through the database maintenance plan the other day, and it looks our vendor has Flushing out procedure cache as one of the steps. The procedure called is a DBCC FLUSHPROCINDB, and this is being done on production database every night. Is there a reason that anyone can think of, that this would be a part of a maintenance plan? Seems like it would be detrimental to performance of the database.
February 11, 2013 at 12:09 pm
How detrimental really depends on what's in the cache. FLUSHPROCINDB is specific to a certain database and doesn't affect any other databases in the procedure cache. Depending on the number of procedures in that database, how often they're called, and of course how complex they are, clearing out the proc cache may not be such a big deal.
By comparison, where I work we have over 4GB in the procedure cache and literally hundreds of rather complex reporting stored procedures...so i don't bother clearing anything out much, and when I want to flush out a particular cached plan I do so for a specific plan handle.
If your procedure list is rather large, it might be a good idea to disable this step for the particular database. Just my $.02
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 11, 2013 at 2:13 pm
Looking at a SQL Trace for one minute, I see that stored procedures and functions get called pretty often by the front end software. I have sent the email to vendor, to see what the reasoning is on their end, still haven't heard from them. Is there a way to monitor how big the proc cache is? Additionally, if lets say I have 26 gigabytes of allocated memory space for SQL, will the growing proc cache be included in that?
February 11, 2013 at 3:28 pm
The plan cache is part of the allocated SQL memory, with 26 GB of allocated memory it'll probably max out at somewhere around 5-6GB, I forget the exact formula.
Regardless, there's little good reason to have that as part of a maintenance plan. If the maint plan rebuilds indexes or updates stats, it'll invalidate the plans that depend on those indexes/stats anyway.
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
February 11, 2013 at 4:10 pm
GilaMonster (2/11/2013)
The plan cache is part of the allocated SQL memory, with 26 GB of allocated memory it'll probably max out at somewhere around 5-6GB, I forget the exact formula.Regardless, there's little good reason to have that as part of a maintenance plan. If the maint plan rebuilds indexes or updates stats, it'll invalidate the plans that depend on those indexes/stats anyway.
Can you explain how the proc cache flush would affect indexes and stats please?
February 11, 2013 at 8:00 pm
Regardless, there's little good reason to have that as part of a maintenance plan. If the maint plan rebuilds indexes or updates stats, it'll invalidate the plans that depend on those indexes/stats anyway.
Procedures remain in the cache for as long as the compiler determines the query plan is still an optimal one. They age, and can often have many similar versions of the query plan. As the statistics against the columns in the underlying tables age or become "stale", the efficiency of the cached plans needs to be reevaluated. SQL Server does this in real-time behind the scenes and determines whether or not it can reuse a particular plan, or if it needs to determine a more efficient one....which it will do on the fly
If you are familiar with viewing execution plans, you'll often notice (especially so in "estimated" plans) and huge difference in the number of estimated rows evaluated by the engine versus the actual number of rows. This can often be a red flag that your statistics are out of date (as many INSERTS/DELETES/UPDATES may have occurred since the last time the statistics were updated.
As Gail has mentioned, if a maintenance plans includes index rebuilds or and update stats operation, then statistics are automagically updated.
EDIT: Flushing the proc cache doesn't effect indexes or stats - Rebuilding indexes or performing a statistics update affects the procedures in the cache. It's basically like flushing all existing procedure plans down the drain, then when a procedure is executed, it's like running for the very first time - ever. This adds a slight performance hit while the compiler determines the most efficient query plan, then retains it in the freshly wiped cache. Over time...the cache grows. These "new" cached plans may still be inefficient if the statistics are out of date, therefore it's generally a good move in a solid maintenance plan to plan for some level of index rebuilds and statistics update.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 12, 2013 at 12:22 am
DVSQL (2/11/2013)
GilaMonster (2/11/2013)
The plan cache is part of the allocated SQL memory, with 26 GB of allocated memory it'll probably max out at somewhere around 5-6GB, I forget the exact formula.Regardless, there's little good reason to have that as part of a maintenance plan. If the maint plan rebuilds indexes or updates stats, it'll invalidate the plans that depend on those indexes/stats anyway.
Can you explain how the proc cache flush would affect indexes and stats please?
Sure. Not at all in any way.
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
February 12, 2013 at 12:28 am
MyDoggieJessie (2/11/2013)
Regardless, there's little good reason to have that as part of a maintenance plan. If the maint plan rebuilds indexes or updates stats, it'll invalidate the plans that depend on those indexes/stats anyway.
Procedures remain in the cache for as long as the compiler determines the query plan is still an optimal one. They age, and can often have many similar versions of the query plan.
Couple points there...
Compiler's not what ages and removes plans.
One procedure or ad-hoc query form will have one plan in cache. If there are many similar ad-hoc query forms, they have have multiple similar plans, but there won't be multiple similar plans for the exact same query, exact same set options
As the statistics against the columns in the underlying tables age or become "stale", the efficiency of the cached plans needs to be reevaluated. SQL Server does this in real-time behind the scenes and determines whether or not it can reuse a particular plan, or if it needs to determine a more efficient one....which it will do on the fly
The evaluation's simple. If the stats haven't changed, the plan's good, no matter how old or stale the stats are. If the stats have changed, the plan's invalid and will be recompiled, no matter how similar the new stats are to the old.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply