Interesting things to watch out for when using the autoclose database option
Here's something which I've been looking at this week, surrounding the autoclose database option.
When you restore any database to a SQL Server 2005 SP2 or later instance, you'll more than likely get errors of this nature written to the log.
2008-11-12 17:24:16.58 spid13s SQL Server has encountered 5 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2008-11-12 17:24:16.58 spid13s SQL Server has encountered 5 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2008-11-12 17:24:16.58 spid13s SQL Server has encountered 5 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
This is expected behavior and is documented in Books Online
"Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval."
I'd been looking at some problems which at first glance appeared to be related to this, in that after restoring a database, there were lots of occurances of cache flush which was causing performance problems.
The answer in this case was that this database was in autoclose mode. A quick check of sp_dboption and this was confirmed. Another quick check to Books Online confirms that autoclose databases will cause a cache flush event when they shutdown.
"When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval."
I'd forgotten about this behavior, as its very rare that i deal with autoclose databases as most of the systems I tend to work with don't use it, autoclose is more designed for desktop systems as books online states.
The act of the cache flush in itself is not particularly problematic, unless of course you constantly access the database and then disconnect from it, causing numerous SQL instance wide cache flushes, which severly impact performance on any server.
Another indication of this type of behavior is that your error log will be full of lots of these messages
2008-11-13 16:10:16.07 spid51 Starting up database 'test'.
2008-11-13 16:10:16.22 spid51 CHECKDB for database 'test' finished without errors on 2008-11-12 16:11:55.453 (local time). This is an informational message only; no user action is required.
2008-11-13 16:10:18.46 spid51 Starting up database 'test'.
2008-11-13 16:10:18.50 spid51 CHECKDB for database 'test' finished without errors on 2008-11-12 16:11:55.453 (local time). This is an informational message only; no user action is required.
The thing that I didn't realize and that really surprised me was that it was possible to create such flushes by just accessing the database through Management Studio. Basically if you were the only user on the database, and you decided to browse its contents in SSMS, you'd open a connection, and then every time that you expanded an object explorer tree control for example, you issue an query to the server to generate the list of objects in that part of the tree (this is easy enough to trace in profiler). However when such a query finishes if you are the only user then the database closes as it deems all active users to be disconnected, and therefore a cache flush event takes place. This of course is a bad thing, especially if you happen to restore your autoclose database to an instance which is running lots of other production databases maintaining large numbers of cache entries on your server.
Just something to think about....
Comments
Anonymous
February 03, 2009
[alternative title: Memory Troubleshooting using DBCC MEMORYSTATUS] The following samples are taken fromAnonymous
February 11, 2009
Last week I wrote about the initial diagnosis of this problem, and now here's the follow up. So if youAnonymous
October 20, 2013
Thanks for sharing the information