HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlModerate

How can I remove a bad execution plan from Azure SQL Database?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
cansqlbadremoveplandatabaseazurehowfromexecution

Problem

DBCC FREEPROCCACHE doesn't work in Azure SQL DB. How else can I force a plan to kick itself out of the cache in a way that won't hurt a production system (i.e. I can't just go alter tables willy nilly)? This is specifically for SQL created by Entity Framework, so these aren't self-managed stored procs - it's effectively dynamic SQL.

(Source was bad indexes -> bad stats, etc. That's all fixed, but a bad plan won't go away.)

UPDATE:
I selected @mrdenny's solution as he got there first. I am, however, successfully using @Aaron Bertrand's script to perform the work. Thanks to everybody for the help!!

Solution

Azure SQL now directly supports this

Azure SQL Database directly supports clearing the proc cache of the current user database without any hacks:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;


Additional Information

The following script (by Shannon Gowen) can be used to watch the process step-by-step:

-- run this script against a user database, not master
-- count number of plans currently in cache
select count(*) from sys.dm_exec_cached_plans;

-- Executing this statement will clear the procedure cache in the current database, which means that all queries will have to recompile.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

-- count number of plans in cache now, after they were cleared from cache
select count(*) from sys.dm_exec_cached_plans;

-- list available plans
select * from sys.dm_exec_cached_plans;

Code Snippets

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
-- run this script against a user database, not master
-- count number of plans currently in cache
select count(*) from sys.dm_exec_cached_plans;

-- Executing this statement will clear the procedure cache in the current database, which means that all queries will have to recompile.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

-- count number of plans in cache now, after they were cleared from cache
select count(*) from sys.dm_exec_cached_plans;

-- list available plans
select * from sys.dm_exec_cached_plans;

Context

StackExchange Database Administrators Q#39689, answer score: 14

Revisions (0)

No revisions yet.