patternsqlModerate
SQL Server has encountered 1 occurrence(s) of cachestore flush
Viewed 0 times
flushsqlencounteredhascachestoreoccurrenceserver
Problem
On one of our production server log files, the following message is being observed on daily basis. What does it mean? Is it a serious issue?
SQL 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.
SQL 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.
Solution
I also saw this in my own environment along with a few other messages:
the 'Bound Trees' cachestore (part of plan cache) due to some
database maintenance or reconfigure operations.
the 'SQL Plans' cachestore (part of plan cache) due to some database
maintenance or reconfigure operations.
the 'Object Plans' cachestore (part of plan cache) due to some
database maintenance or reconfigure operations.
It turns out there were multiple databases with the AutoClose setting turned on.
You can check your own database with this command:
Once we turned the setting off, the messages went away and performance improved. For further reading as to why auto close is a bad idea:
- SQL 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.
- SQL 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.
- SQL 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.
It turns out there were multiple databases with the AutoClose setting turned on.
You can check your own database with this command:
SELECT DATABASEPROPERTYEX('DatabaseName','IsAutoClose')Once we turned the setting off, the messages went away and performance improved. For further reading as to why auto close is a bad idea:
- Worst Practice: Allowing AutoClose on SQL Server
- SQL Server Best Practices: AutoClose should be off
Code Snippets
SELECT DATABASEPROPERTYEX('DatabaseName','IsAutoClose')Context
StackExchange Database Administrators Q#21302, answer score: 13
Revisions (0)
No revisions yet.