Mitch Wheat - Treat the cause, not the symptoms!

For on-premise SQL Servers you can run

dbcc freeproccache

which clears the entire server cache (provided you have the ALTER SERVER STATE permission). Many online resources and SQL Server Books Online give scary warnings about running this, but running DBCC FREEPROCCACHE will cause very few problems, even on a busy OLTP system. It will cause a small CPU spike for a few seconds as query plans get recompiled. It can be a useful tool when base-lining expensive queries or stored procedures.

If that’s not selective enough, you can free the cached plans for a single database using an undocumented DBCC command, FLUSHPROCINDB:

-- Flush all plans from the plan cache for a single database  
declare @dbid int;
select @dbid = dbid from master.dbo.sysdatabases where name = 'MyDatabaseName';

dbcc flushprocindb(@dbid);

If you want to remove a single plan from the cache:

-- Get the plan handle (varbinary(64)) for a cached query plan
select 
    cp.plan_handle, 
    st.text
from 
    sys.dm_exec_cached_plans cp 
    cross apply sys.dm_exec_sql_text(plan_handle) st
where 
    text LIKE N'%GetJournal%';

-- Remove a specific plan from the cache using its plan handle
dbcc freeproccache (0x060050000C267C1030CE4EC70300000001000000000000000000000000000000000000000000000000000000);

DBCC FREEPROCCACHE is not supported in SQL Azure as that wouldn’t be practical in a multi-tenanted database environment. SQL Azure (and SQL Server 2016) has introduced a new mechanism for clearing the query plans for a single database:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

ALTER DATABASE SCOPED CONFIGURATION

This statement enables the configuration of a number of database configuration settings at the individual database level, independent of these settings for any other database. This statement is available in both SQL Database V12 [SQL Azure] and in SQL Server 2016. These options are:

• Clear procedure cache.

• Set the MAXDOP parameter to an arbitrary value (1,2, ...) for the primary database based on what works best for that particular database and set a different value (e.g. 0) for all secondary database used (e.g. for reporting queries).

• Set the query optimizer cardinality estimation model independent of the database to compatibility level.

• Enable or disable parameter sniffing at the database level.

• Enable or disable query optimization hotfixes at the database level.

Database Scoped Configuration

Labels: SQL Azure, SQL Server