March 20, 2009 at 4:30 am
We are having Sql 2005 express edition on our server which has been used by a ASP.NET web site. One day during macfee scan we received sql server connectivity issue and after restart problem has been solved but now daily we receive the error in sql server log
SQL Server has encountered 4 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Because of this Queries not perform as expected as mentioned in http://support.microsoft.com/default.aspx/kb/917828
Please let me how can i solve the problem..Thanks in advance..
March 20, 2009 at 7:58 am
You need to identify what's causing the cache store flush. Is it happening regularly?
It could be someone running DBCC FREEPROCCACHE
It could be a database that's set to autoclose
It could be a database restore.
It could be someone running sp_configure
There are a few other things that could cause that as well.
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
March 20, 2009 at 8:05 am
second option see some logs.. since whn problem got started ....
03:39.8Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
03:40.0ServerSQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
03:40.0ServerDetected 1 CPUs. This is an informational message; no user action is required.
03:43.2ServerUsing dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
03:47.7ServerDatabase mirroring has been enabled on this instance of SQL Server.
03:48.3spid5sStarting up database 'master'.
03:50.8spid5sRecovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
03:53.5spid5sSQL Trace ID 1 was started by login "sa".
03:53.9spid5sStarting up database 'mssqlsystemresource'.
03:54.1spid5sThe resource database build version is 9.00.4035. This is an informational message only. No user action is required.
03:57.1spid8sStarting up database 'model'.
March 20, 2009 at 8:17 am
achleshsoni (3/20/2009)
second option see some logs.. since whn problem got started ....
Sorry, I don't understand.
There's nothing interesting in that piece of log, it's a standard set of SQL startup events.
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
March 20, 2009 at 8:26 am
yeah... we know issue started sometime after SQL Server startup -which is what log is showing - but, you may want to pin-point the issue a little more carefully. 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 20, 2009 at 8:32 am
no but this happened suddenly while sql is fully working , it looks bcas of overload of memory ...
output of following command is below..
SELECT * FROM SYS.DM_OS_SYS_INFO
Result :
852285273438 85228515 1 10000 1 1073172480 2147352576 19498 25403 25403 520192 40000 5 32 256 1 5 208
can see some buffer overload here...
March 20, 2009 at 8:34 am
achleshsoni (3/20/2009)
no but this happened suddenly while sql is fully working , it looks bcas of overload of memory ...
High memory usage will not cause a complete cache flush.
Check the times that you see the entries in the log and see if those times correlate with a job. If so, check the job. If not, you may have to do a trace (Profiler/SQL Trace) to see what's running and who's running it.
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
March 20, 2009 at 8:40 am
:w00t: Buffer overload?
Please check this http://blogs.msdn.com/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 21, 2009 at 3:33 am
A lot of things could be the cause like memory pressure, poor plan reuse, auto update stats, reindexing, ddl etc. Unless you are having performance problems, they can be disregarded.
Few text from http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/e91683e8-ebf1-4873-846a-504baefc3428/[/url]
But my problem is not solved,I am having 1GB RAM and usage graph shows 95% has been utilized? Can this be case? can upgrade to profession version of sql from express edition will solve the problem? Or can i increase the buffer size( and how)? IS there some limitation of 4.9 GB per database(Can i cross this limit and how)?
I appreciate your feedback in advance...
March 21, 2009 at 4:37 am
achleshsoni (3/21/2009)
A lot of things could be the cause like memory pressure, poor plan reuse, auto update stats, reindexing, ddl etc.
None of the things you list there will cause SQL to completely flush it's procedure cache.
Memory pressure will cause all caches to be reduced in size. That will not give the message that you saw.
Poor plan reuse means that the cache hit ratio is lower and more queries have to compile. It will not cause SQL to completely flush it's cache.
Auto update stats allows SQL to automatically update stats when it runs queries if they are stale. If the stats are updates then plans based on those stats will be invalidated and recompiled on the next execution. It will not result in SQL competely flushing its cache.
Reindexing will invalidate any plans that use the rebuilt index. Those queries will recompile on the next execution. It will not result in SQL completely flushing it's cache.
DDL will invalidate any plans based on the altered object. Those queries will recompile on the next execution. It will not result in SQL completely flushing it's cache.
The things that cause a complete cache flush, and the message in the error log that you listed is given in the link that Paul posted. No other operation will cause a compete cache flush
But my problem is not solved,I am having 1GB RAM and usage graph shows 95% has been utilized? Can this be case?
Easily. SQL uses a lot of memory. If it's using too much, limit the server's max memory (server settings or sp_configure)
can upgrade to profession version of sql from express edition will solve the problem?
What is going to solve the cache flush messages is for you to find the operation that's causing them and not run it again.
Or can i increase the buffer size( and how)?
If you've only got 1 GB of memory on that machine, then SQL cannot use more than (probably) 600MB or so, as the OS also needs memory
IS there some limitation of 4.9 GB per database
No.
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
March 24, 2009 at 8:10 am
One interesting thing we can see from this following log text is that each time before cachestore flush 'aspnetdb' got restarted.. what can we do here now?
2009-03-23 08:20:18.190spid89Starting up database 'aspnetdb'.
2009-03-23 08:20:18.770spid8sSQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-03-23 08:20:18.770spid8sSQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-03-23 08:20:18.770spid8sSQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-03-23 08:20:18.950spid89Starting up database 'aspnetdb'.
2009-03-23 08:20:20.070spid89Starting up database 'aspnetdb'.
2009-03-23 08:20:20.440spid89Starting up database 'aspnetdb'.
2009-03-23 08:21:03.030spid89Process ID 85 was killed by hostname XXXX, host process ID 2552.
2009-03-23 08:21:47.320spid89Starting up database 'aspnetdb'.
2009-03-23 08:21:48.100spid89Starting up database 'aspnetdb'.
2009-03-23 08:22:32.210spid89Starting up database 'aspnetdb'.
2009-03-24 05:00:21.340spid67Starting up database 'aspnetdb'.
2009-03-24 05:00:22.110spid15sSQL Server has encountered 11 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-03-24 05:00:22.130spid15sSQL Server has encountered 11 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-03-24 05:00:22.130spid15sSQL Server has encountered 11 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
March 24, 2009 at 8:26 am
The database aspnetdb is set to autoclose. The close of the DB is causing the cache flush.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply