How to find and remove a query plan from the plan cache in SQL Server


In this article I will try to explain how we can obtain the plan of a SQL query from the plan cache and also how we can remove the plan from the cache keeping other plans intact. Before we see how we can achieve the above we will understand few basic things on plan cache.
Structure
1) What is a query plan: A query plan is an organized and ordered set of steps used by the SQL engine to fetch data. Every query has a query plan cached in the SQL memory
2) What is a plan cache: Apart from Buffer pool which is the biggest consumer of SQL Server memory, SQL Server allocates a proportion of this memory for use of storing the query plans. This storage of query plans in the SQL memory is called plan cache. The storage is a volatile storage and if the SQL server is restarted then all the plans are removed from this cache.
3) What is a plan handle: A plan handle is an unique identifier of the plan.

Let us now see how we can locate the query plan of an already executed query:
Step 1: In this example I have executed the below query on the Adventureworks2012 database.

SELECT [BusinessEntityID],[Title]
,[FirstName],[MiddleName]
,[LastName],[Suffix]
,[JobTitle],[PhoneNumber]
,[PhoneNumberType],[EmailAddress]
,[EmailPromotion],[AddressLine1]
,[AddressLine2],[City]
,[StateProvinceName],[PostalCode]
,[CountryRegionName],[AdditionalContactInfo]
FROM [AdventureWorks2012].[HumanResources].[vEmployee]
WHERE PostalCode = '98104'

Step 2: Now let us find the query in the plan cache. We will use the below query to find the plan of the query from the plan cache. Observe carefully that in line number 11 we have used a filter to search all the queries in the plan cache which has the word ‘PostalCode’ which is also a part of our executed code on the Adventureworks2012 database.

select a.plan_handle,query_plan,text,
execution_count,size_in_bytes,
creation_time,usecounts,
last_execution_time,execution_count,
total_elapsed_time,last_elapsed_time
from sys.dm_exec_cached_plans a
inner join sys.dm_exec_query_stats b
on a.plan_handle=b.plan_handle
cross apply sys.dm_exec_sql_text(b.sql_handle) as sql_text
cross apply sys.dm_exec_query_plan(a.plan_handle) as query_plan
where text LIKE '%PostalCode%'

Snapshot of the output below
Qp1
Step 3: The highlighted text is the query that we ran and you can see the the query plan associated with it on the second column. Click on the plan and it will open on a new window.
qp2
qp3
So now we know how we can find the plan of an already executed query from the cache. In short use the above mentioned query and use a string from the executed query as filter in the above given query.

Now let us see how we can remove the query plan from the plan cache
Step 1: Find the plan handle of the query plan.
qp4
Step 2: Execute the below code to drop the plan from the plan cache

DBCC freeproccache(0x06000100A8CEE12640BB20D50200000001000000000000000000000000000000000000000000000000000000)

This will only drop that particular plan from the plan cache.Be very careful that you do not practice this on a production environment without understanding the implications of the absence of the query plan which you dropped.

I hope this article was helpful in finding and removing query plans from the plan cache without impacting other plans.

Advertisements

Tagged: , , , , , ,

One thought on “How to find and remove a query plan from the plan cache in SQL Server

  1. […] How to find and remove a query plan from the plan cache in SQL Server (appliedsql.wordpress.com) […]

    Like

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

%d bloggers like this: