Changing recovery model removes plans from procedure cache

Did you know that when you change the recovery model of a database – plans for that database are removed from cache ? I didn’t…

SQL 2012 :-

I was doing some performance tuning today, using DMV sys.dm_exec_query_stats to look at summary information for database performance. I normally find this a very useful DMV as a starting point for any database tuning. However, to my surprise, today this DMV held very few rows for the database I was attempting to tune.

A little bit of investigation revealed that my oldest plan in cache for this database was created just after the recovery model had been changed from FULL to SIMPLE. Now, I know that certain SQL Server operations can flush cache, for example changing the max degree of parallelism on a server – but I’d never come across this before..

I wonder how many DBAs are inadvertently flushing procedure cache if they are switching recovery models often – for example, to help with bulk-logged performance ?

Here’s a simple test script to demonstrate :-

1. Create a database

USE Master
GO
CREATE DATABASE PlanCacheTest

2. Confirm the recovery model is full.

SELECT name, recovery_model_desc FROM sys.databases

3 Create a test proc in the database.

USE PlanCacheTest

CREATE PROC PlanCacheProc 
AS 
SELECT * FROM sysindexes ORDER BY dpages DESC

4. Run the proc and check there is an entry in the procedure cache now

EXEC PlanCacheProc 

SELECT * 
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 
WHERE  qt.dbid = DB_ID('PlanCacheTest')

Will return 1 row.

5. Now change the recovery model

ALTER DATABASE  PlanCacheTestSET RECOVERY SIMPLE

6. Now look in the procedure cache again, the plan for my new proc has gone…

SELECT * 
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 
WHERE  qt.dbid = DB_ID('PlanCacheTest')

Will return 0 rows.

USE Master
GO
DROP DATABASE PlanCacheTest

I see the same behaviour switching between all 3 recovery models.