October 14, 2014 at 4:39 am
Was wondering, when performance changes have been made to existing code and deployed to a production environment, should anything be done regarding clearing old cache?
Is there a risk of the new code picking up cold or wrong cached query plans etc?
I know you can't clear the cache for the full system as it will affect everything else. But are the ways to target the changes? I.E run the new code with an OPTION (RECOMPILE) few times to build up new clean plans for it to use?
Or will the update stats see it is using bad plans and then start creating new clean ones over time?
October 14, 2014 at 4:41 am
What were the changes?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 14, 2014 at 4:42 am
partial re-writes of stored procedures on how the aggregations are done.
October 14, 2014 at 4:45 am
roblew 15918 (10/14/2014)
partial re-writes of stored procedures on how the aggregations are done.
According to BOL, this should cause a recompile of the query plan.
Execution Plan Caching and Reuse
The conditions that invalidate a plan include the following:
* Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
* Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
* Changes to any indexes used by the execution plan.
* ...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 14, 2014 at 4:49 am
ah that's great! many thanks
October 14, 2014 at 11:27 am
Changes to a procedure will mark it for recompile if a plan for that proc exists in cache. You can't get the old code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply