Articles → SQL SERVER → Plan Cache In Execution Plan

Plan Cache In Execution Plan

In the Introduction to execution plans tutorial, we have seen how the process of creating an execution plan goes from the time the query is submitted till the execution plan is created. But it is an expensive option to create an execution plan again and again for the same query.

For that reason, the SQL server stores the plan in a memory location called a plan cache. During the Algebrizer process, a hash is created which is for uniquely identifying the query.

So, when the query is submitted to the relational engine, the SQL server checks if the plan is already present in the plan cache. If yes, then all the expensive process for the creation of execution plan is skipped and plan stored in plan cache is reused.

How To Check Plan Cache In SQL Server

Plan cache is stored in "syscacheobjects" table in master database. If you want to get the list of plans stored in SQL server, you can fire this query.

Use Master 
Go 
select 
  * 
from 
  syscacheobjects

You will get following output.

Picture showing the list of plans in the syscacheobjects table

Here only those records are cached plan where the value of "cacheobjtype" is "Compiled Plan". If you scroll the result set to the right you could see the query for which plan is created. See the screen shot below.

Picture showing the SQL query for which plan is created

So, every time you submit a query a record is added into the table.

How To Clear Plan Cache

You can use following query to clear plan cache: -

Use Master
Go
dbcc freeproccache

Posted By -	Karan Gupta

Posted On -	Thursday, April 2, 2015

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**