You might have seen the below messages in SQL Server error log at some point of time –

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.
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 ‘Bound Trees’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

In the event you see this message,your next course of action should be checking if any of the databases are having property ‘AutoClose’  is set to True.

There are other conditions too which triggers this message,however you dont want your databases to have AutoClose set as True for production environment at all.

We can query the sys.databases view to get this details pretty easy.

--Query the sys_databases view for autoclose details
SELECT name 
FROM   sys.databases 
WHERE  is_auto_close_on <> 0

Obviously this question comes to your mind – What is the relation between Auto Close being turned ON and this cachestore flush message ?

The answer to that question is pretty much explained well in MSDN.

 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.

Lets do a quick demo to demonstrate this behaviour.

Demo

1. Using Adventureworks database running on SQL Server 2008 R2

2. I will now do a simple SELECT * from one of the tables.I will run the statement twice just to increase the execution count.

--Select query to pull the records
SELECT * 
FROM   production.WorkOrder

3. I will now query the plan cache to retrive the details of SELECT statement ran above

--Query plan cache for finding the SELECT Statement
SELECT CP.objtype                          AS ObjectType, 
       Object_name(SXT.objectid, SXT.dbid) AS ObjectName, 
       CP.usecounts                        AS ExecutionCount, 
       SXT.TEXT                            AS QueryText, 
       CP.plan_handle 
FROM   sys.dm_exec_cached_plans AS CP 
       CROSS apply sys.Dm_exec_sql_text(CP.plan_handle) AS SXT 
WHERE  CP.usecounts = 2

You can clearly see the Query Text(Select statement) and the plan_handle in the cache.Note down the plan_handle as it will be required later on

0x06000800557BB714B8E0F30A000000000000000000000000

4. Now we will set Auto Close property for the database Adventureworks as True.

--Set Autoclose to ON
USE [master]  go  ALTER DATABASE [Adventureworks]  SET auto_close ON WITH no_wait  go 

5. Next we will again query the plan cache to confirm that the details have been flushed as the Auto Close property was turned ON and the database was closed.

--Query plan cache for finding the SELECT Statement
SELECT CP.objtype                          AS ObjectType, 
       Object_name(SXT.objectid, SXT.dbid) AS ObjectName, 
       CP.usecounts                        AS ExecutionCount, 
       SXT.TEXT                            AS QueryText, 
       CP.plan_handle 
FROM   sys.dm_exec_cached_plans AS CP 
       CROSS apply sys.Dm_exec_sql_text(CP.plan_handle) AS SXT 
WHERE plan_handle = 0x06000800557BB714B8E0F30A000000000000000000000000

(0 row(s) affected)

Conclusion

Next time when you see this error make sure that Auto Close property is definitely cross checked and ensure that its not turned ON.

Yesterday there was an interesting discussion related to OLEDB wait type getting spiked when DBCC is executed.

This was pretty interesting question for me and was curious to really understand if OLEDB wait type has a dependency with DBCC.

OLEDB is a common wait type in SQL Server.There was a survey conducted by the well know SQL Server Expert and Master Paul S Randal(Blog/Twitter) 2 years back and OLEDB was one of the top 10 waits found across multiple environments.

This wait type indicates that an SPID has made a function call to an OLE DB provider and is waiting for the function to return the required data. This wait type may also indicate that the SPID is waiting for remote procedure calls, linked server queries, BULK INSERT commands or full-search queries.

We inquired with the person who posted the question about OLEDB wait and DBCC relation if he was running any linked servers and the answer was NO.He confirmed that there are no queries which are running while the DBCC was running.There are some DMV’s which might be triggering OLEDB,however as there was no other queries were running it was obvious to check if DBCC really uses OLEDB internally.

The short answer to confirm the relation is,Yes! DBCC uses OLEDB internally and a small spike is expected.

Its normal to get a spike in OLEDB wait,however the average wait_time will be relatively less when you compare this wait with other waits.

If you are using linked servers,full text search and observing OLEDB as a potential candidate of wait type,then you will need to trouble shoot and isolate the issue.

Below is a quick demo to show a increase in this wait type when you run DBCC.

1. Below is the Wait Stats before running DBCC

2. We will run DBCC for Adventureworks 2012 database

3. Below is Wait Stats after running DBCC

As you see there is a small spike in the values and you could even run DBCC on a huge database and see how much spike you are experiancing.

Conclusion

If DBCC is running fine and is not running long duration than expected,then this wait type is not necessarly a cause for concern.

I am interested to understand if you are facing any issues with this OLEDB wait type in your environments and what course of action you have done.

Thanks for reading.

 Over the last 2 years I have been working with multiple clients and one of the common situations which I observed when dealing with prebuilt script(Application setup scripts) is having an additional log file(ldf) getting created during initial database setup.

This additional log file is created mostly with a wrong assumption that it will improve performance. I would like to clarify this concept using this blog post.

Adding additional log file will never gain you performance as log records are written sequentially to the transaction log.

Adding an additional log file in a different drive during an event where the initial file is full and you don’t have enough space on the disk to allow growth is acceptable.