Share via

SQL Server Troubleshooting: How to remove a bad execution plan from plan cache

  • Article

Overview

The following article was previously created as a blog in Portuguese, which was posted here. This is the English translation of a blog, with some modifications to fit best to the TechNet WIKI 

Everything is going well, your SQL server is with low CPU utilization, high Page Life Expectancy, few fragmented indexes and updated statistics when 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

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

https://felipelauffer.files.wordpress.com/2016/06/screen-shot-2016-06-17-at-11-52-10-pm1.png

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>)
DBCC FREEPROCCACHE (0x0600060020527003B0928B53CC0100000100000000000000000)

https://felipelauffer.files.wordpress.com/2016/06/screen-shot-2016-06-18-at-12-24-18-am.png

Done! The execution plan for your query has been successfully removed from the SQL Server plan cache and probably your server will reduce the high resource utilization and return to respond normally. After this scare try to improve the performance of your query and understand why the execution plan has been altered.

 

Caution
  1. Use this procedure as a workaround and not as a definitive solution to the problem. Look to improve query performance and understand what is causing the execution plan changes.
  2. Never use the DBCC command FREEPROCCACHE without the content inside the parentheses, especially in production environments, as this will remove ALL execution plans from SQL Server plan cache.

 

Reference