I was curious to look at the effect of database options, on the SQL server plan cache.

I have restored the Adventure works sample database onto my SQL Server 2008 R2 RTM instance four times as AW1, AW2, AW3 and AW4.

I have used, and slightly modified, a query from Kalen Delaney‘s awesome SQL Server 2008 Internals to look at the contents of the plan cache:

select DB_NAME(dbid) as [db_name],usecounts,cacheobjtype,objectid,[text]
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(plan_handle) as s
where cacheobjtype = 'Compiled Plan'
and [text] not like '%dm_exec_cached_plans%'
and [text] like '%Production.Product%';

(I needed the [text] like ‘%Production.Product%’ filter because other processes where running on my server creating noise in the result set.)

First clear my plan cache:

dbcc freeproccache

Then I will run adhoc queries against all four databases:

select * from AW1.Production.Product where ListPrice <> 0
go
select * from AW2.Production.Product where ListPrice <> 0
go
select * from AW3.Production.Product where ListPrice <> 0
go
select * from AW4.Production.Product where ListPrice <> 0
go

I have used go between the selects here because I want each select to be a separate batch – and thus generate a separate plan.

Use Kalen’s query to check the plan cache:

select DB_NAME(dbid) as [db_name],usecounts,cacheobjtype,objectid,[text]
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(plan_handle) as s
where cacheobjtype = 'Compiled Plan'
and [text] not like '%dm_exec_cached_plans%'
and [text] like '%Production.Product%';

And get:

The db_name column is returning a null value because these are adhoc plans. I can create database specific plans by creating and running procedures:

use AW1
go
create procedure dbo.up_plancachetest
as
select * from AW1.Production.Product where ListPrice <> 0
go
exec AW1..up_plancachetest
use AW2
go
create procedure dbo.up_plancachetest
as
select * from AW2.Production.Product where ListPrice <> 0
go
exec AW2..up_plancachetest
use AW3
go
create procedure dbo.up_plancachetest
as
select * from AW3.Production.Product where ListPrice <> 0
go
exec AW3..up_plancachetest
use AW4
go
create procedure dbo.up_plancachetest
as
select * from AW4.Production.Product where ListPrice <> 0
go
exec AW4..up_plancachetest

And now I when I query the cache:

Now I want to see what happens to the cache after various database operations.

Let’s set AW1 to autoclose, BOL tells us that “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 and higher, 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.“

ALTER DATABASE AW1 SET auto_close on
go

And query the plan cache:

Set AW2 offline:

ALTER DATABASE AW2 SET offline WITH ROLLBACK IMMEDIATE
go

Query the cache:

And finally drop AW3:

ALTER DATABASE AW3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE AW3 
go

And query the cache:

Conclusion: It looks like SQL Server has become more discriminating in the way it flushes out the plan cache since 2005. I’d be very interested to hear from anyone keen to try the above experiment on 2008 or on different SP levels.