Disclaimer: Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Introduction:

let us assume, everything is going well, your SQL server is with low CPU utilization, high Page Life Expectancy, few fragmented indexes and updated statistics but suddenly some query get a bad execution plan and the CPU usage rises to 100% and you notice that there are multiple sessions of the same application running this same query, which until then had never presented problem, taking too long to return the result and are flooding the SQL Server and engine getting out of resources. What would you do??

This article presents a simple way to remove this bad execution plan from the SQL Server plan cache and save you from an emergency.

First you need the content of sql_handle or plan_handle column of the query that is having trouble to remove the execution plan from the SQL Server plan cache.

Finding the plan_handle

To capture the sql_handle or plan_handle of the query, you can use only one dynamic management view (DMV) and one dynamic management functions (DMF): sys.dm_exec_query_stats and sys.dm_exec_sql_text. 

You can query these two objects and filter it using a query snippet that you know that is causing the trouble and sort by totalDurationms or totalCPUms in descending order, as shown below:

SELECT TOP 10
    execution_count,
    total_elapsed_time / 1000 as totalDurationms,
    total_worker_time / 1000 as totalCPUms,
    total_logical_reads,
    total_physical_reads,
    t.text,
    sql_handle,
    plan_handle
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) as t
ORDER BY total_elapsed_time DESC

Copy the content of plan_handle or sql_handle column of the exact query that is causing the trouble.

Removing Plan Handle from the Plan Cache

Insert the copied content inside the parentheses of the DBCC FREEPROCCACHE command, as shown below:

--DBCC FREEPROCCACHE (<copy the plan_handle here>)

Example:

DBCC FREEPROCCACHE (0x0600060020527003B0928B53CC0100000100000000000000000)

Done! Thus the execution plan for your query will get removed successfully from the SQL Server plan cache and probably your server will reduce the high resource utilization and return to respond normally. later you can try working on improving the performance of your query and understand why the execution plan has been altered.

From Azure SQL database:

As we know, DBCC FREEPROCCACHE doesn’t work in Azure SQL DB

So , Azure SQL database support below code.

-- 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;

Hope this helps!