How To Flush An Execution Plan For A Statement On SQL Server

Many times, need to probe differents executions plan for the same statement where perform tuning. It is a good practise flush the previous plan before. You can flush the plan from the cache with DBCC command. This testcase is on SQL Server 2012, it works on 2014 too.

First, locate the sentence what you want flush and its plan_handle, you can do it with the following query;

SELECT 
   PLAN_HANDLE, 
   B.TEXT
FROM 
   SYS.DM_EXEC_CACHED_PLANS 
   CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) B
WHERE 
   TEXT LIKE N'%SELECT COUNT (*) FROM MY_TABLE%';   -- HERE YOU MUST SPECIFY YOUR QUERY OR SUBSTRING

Second, with the plan_handle got, execute the DBCC command

DBCC FREEPROCCACHE (0x060005001D724F342057CE2B0200000001000000000000000000000000000000000000000000000000000000);

If you execute the query from first step, it returned nothing.

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s