Changing recovery model removes plans from procedure cache for a database ?
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 2008 :-
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
CREATE DATABASE stevetest
2. Confirm the recovery model is full.
SELECT name, recovery_model_desc FROM sys.databases
3 Create a test proc in the database.
USE stevetest
CREATE PROC steveproc
AS
SELECT * FROM sysindexes ORDER BY dpages DESC
4. Run the proc and check there is an entry in the procedure cache now
EXEC steveproc
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('stevetest')
Will return 1 row.
5. Now change the recovery model
ALTER DATABASE database stevetest SET 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('stevetest')
Will return 0 rows.
I see the same behaviour switching between all 3 recovery models.