January 15, 2008 at 9:19 am
Hi
I have lots of following message in my event viewer on sQL Server :
SQL Server has encountered 3 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
or
SQL Server has encountered 3 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
or
SQL Server has encountered 3 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
I don't think we did some maintenance plans during the day.
Is this a memory/CPU consuming issue ? What can we do about it ?
Many thx in advance
El Jefe
JV
January 15, 2008 at 11:42 am
For Every Restore operation you will get those 3 entries recorded in the log...
* Noel
January 15, 2008 at 3:55 pm
if you didn't restore a database or do anything which might flush the cache then you may have a problem. Depends on your setup and service pack
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 16, 2008 at 12:57 am
Hi
We didn't restore any database.
We are working with SQL 2005 SP2
What can we do about it ?
thx in advance
JV
January 16, 2008 at 1:29 am
The cache for a DB gets flushed when the DB is restored, taken offline or detached. Also when some ALTER Database statements are run.
The cache for the entire server gets flushed when some configuration changes are made (I don't recall which ones)
And then there's the explicit DBCC statements to empty the cache.
You should try and identify what happend to cause that. Most of the operations that will flush a cche require fairly high permissions.
Cache flushed aren't nice on busy servers, because SQL then has to repopulate all the caches. It causes slowdowns.
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
January 16, 2008 at 2:36 am
Might be worth looking at this link:
http://blogs.msdn.com/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx
Pretty much covers what GilaMonster has said, incl config and DBCC changes.
Wondering if you've got someone testing code runs and clearing down the cache, or if some script recently implemented (based on the first time these errors started to appear in the ErrorLog) has any of the DBCC's or config changes in it. Hopefully, if you're using change control, you may be able to isolate what batch of implemented scripts may hold the culprit Another possibility is if you have any DBs set to 'auto close'.
Possibly use Profiler to see if you can capture the events that occur just before/around the ErrorLog event times, incl who is firing the events.
HTH
January 16, 2008 at 3:06 am
Hi
We did add some memory to the server.
Can that be the cause ?
Thx
JV
January 16, 2008 at 3:10 am
Hot add memory? Don't think it should.
Did you change the SQL Server's max and min memory after doing that?
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
January 16, 2008 at 3:13 am
No I didn't
Server was off line when added.
Kind regards
El Jefe
JV
January 16, 2008 at 3:19 am
Then it would not have been the cause.
A cache flush message happend when some operation, while SQL is running, forced the cache to be emptied.
If none of your databases are set to auto-close, then you will need to use profiler to identify the cause. You can corrolate the event's time with the time of the message in the error log.
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
January 16, 2008 at 1:40 pm
ok - this can happen - please give details of sql edition, o/s ( including sp's ) is this x32 or x64, how much memory, what are your memory settings on sql server and what else is running on this box?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 16, 2008 at 1:41 pm
sorry - if x32 what switches are in boot.ini
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 17, 2008 at 12:27 am
Hi
It's X64 and from 4Gb tot 7Gb
JV
February 14, 2008 at 12:36 pm
Do you have any routines that change the query governor threshold? if so, i have seen these logs entries popup some time after the threshold was changed.
February 19, 2008 at 2:28 pm
I posted this - it may help - follow on some of the links
usual stuff applies - if you have ent x64 sql then you need to set lock pages in memory and make sure max memory has been set. You might also want to culm update 5, sp2 is not suficient for x64 I've found. I've found that configuring x64 servers is not so easy as x32.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply