May 3, 2008 at 12:45 pm
This affects SQL 2005, not entirely sure yet whether SQL 2000 acts in this way.
Having set up logshipping to a secondary server that acts as the live server for a number of other important databases (I am going for active\active logshipping pairs to make better use of my hardware) I noticed the following messages in my errorlog after every log restore:
2008-05-03 18:10:44.480 spid63 SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2008-05-03 18:10:44.630 spid63 SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2008-05-03 18:10:44.630 spid63 SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
so I decided to do some research and came across the following article in an MS development team blog:
http://blogs.msdn.com/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx
please note the comment 'Currently, due to resource constraints, we do not have plans to do DB specific plan cache flushing. We certainly will keep your feedback in mind going forward.'
Also scary all the other things around alter database that clear the proccache, including that old no-no autoclose (leave it OFF)
So, not too happy with this I have posted feedback on MS Connect, please VOTE.
https://connect.microsoft.com/Connect/feedback/ViewFeedback.aspx?FeedbackID=341803
The following are the results from dbcc procache just before the log restore and just after it started:
-----------------------
2008-05-03 17:59:59.087
(1 row(s) affected)
num proc buffs num proc buffs used num proc buffs active proc cache size proc cache used proc cache active
-------------------- -------------------- --------------------- -------------------- -------------------- --------------------
101309 4788 4788 30451 1482 1482
-----------------------
2008-05-03 18:01:08.920
(1 row(s) affected)
num proc buffs num proc buffs used num proc buffs active proc cache size proc cache used proc cache active
-------------------- -------------------- --------------------- -------------------- -------------------- --------------------
1269 188 188 66 5 5
---------------------------------------------------------------------
May 3, 2008 at 1:08 pm
just watched my proccache on a 2000 box before\after a log restore and did not see this behaviour..............
---------------------------------------------------------------------
May 4, 2008 at 3:27 pm
Certain sp_configures also flush the cache. I don't know offhand which ones. In SQL 2000 prior to SP4 (I think) it was all changes done using sp_configure
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
May 8, 2008 at 3:54 pm
in case there is anyone else out there who would rather their proccache was not cleared down just because they restored a log backup, the connect suggestion has moved to:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=342832
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply