October 22, 2009 at 11:49 am
We're having a performance problem that I'm getting nowhere with. The Perfmon counter "SQLServer: Plan Cache: Cache Object Counts" is dropping to zero periodically, when a certain event happens.
The SQL servers where this is happening are configured like this... A "wrapper" database points to one of two databases in a READONLY, STANDBY status. Periodically, transaction logs are applied to one of the two underlying databases (to update it with new data), then the wrapper database switches to the newly-updated DB. When this switch happens, the Cache Object Counts drops to zero, the "SQLServer: SQL Statistics:SQL Compilations/sec counter spikes, and query execution times go way up for a brief period.
Why would SQL Server be flushing the cache? Any ideas how I could even find out?
1. There is 43GB of free memory on the server, and the Buffer Manager has hundreds of thousands of free pages. The service account (a local Windows account) has the "Lock Pages In Memory" credential, and is a local admin. AWE is enabled.
2. There is no call to DBCC FreeProcCache() on the servers, nor is there a call to it on the server generating the transaction logs.
3. This is SQL 2005 x64 Standard Edition (Build 9.00.3161), so I can't use DB Snapshots to replace the Wrapper database - but I have no idea if that would be feasible, anyway).
October 22, 2009 at 12:14 pm
Is auto_close property turned ON for any of these databases?
MJ
October 22, 2009 at 12:19 pm
any type of database restore event on any database in an instance will clear the proc cache, this is what you are seeing.
This is intended behavior. you should also see messages in the errorlog at the same time showing objects have been cleared from cache.
---------------------------------------------------------------------
October 22, 2009 at 1:22 pm
We don't have Cursor Close on Commit enabled on the DB in question. What effect would that have (other than what can be guessed from reading the name of it).
The flush on restore thing is news to me. That's server-wide? Not just a single database? That's this message, here?
[font="Courier New"] SQL Server has encountered 24 occurrence(s) of cachestore flush for the 'SQL Plans'
cachestore (part of plan cache) due to some database maintenance or reconfigure operations.[/font]
I knew that was there, but I thought it was because we clear the permissions cache daily...
October 22, 2009 at 2:54 pm
cjgilson (10/22/2009)
We don't have Cursor Close on Commit enabled on the DB in question. What effect would that have (other than what can be guessed from reading the name of it).The flush on restore thing is news to me. That's server-wide? Not just a single database? That's this message, here?
[font="Courier New"] SQL Server has encountered 24 occurrence(s) of cachestore flush for the 'SQL Plans'
cachestore (part of plan cache) due to some database maintenance or reconfigure operations.[/font]
I knew that was there, but I thought it was because we clear the permissions cache daily...
yep, thats server wide. annoying isn't it. I put a request in on connect to change that behaviour and advertised the fact here, but no one saw fit to vote on it to my surprise. However Microsoft did reply that they intend to change the behaviour to flush the cache for the database being restored only at 'some time in the future'
and yes thats the message you get. Tie it up to your restores, they will occur every time you do a restore.
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply