Thursday, September 10, 2009

Droping a snapshot DB will cause procedure cache flush

Here is something to you should be aware of:
In SQL Server 2005 (not in 2008) when you drop
a snapshot database, the entire procedure cache of
the instance of SQL Server will be cleared (flushed) !

It will cause a recompilation of all subsequent execution plans
and you will see temporary excessive CPU usage and
decrease in query performance.

For more information:
http://support.microsoft.com/kb/917828
http://blogs.msdn.com/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx

No comments:

Post a Comment