Articles → SQL SERVER → Plan Cache In Execution PlanPlan Cache In Execution PlanIn 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 syscacheobjectsYou will get following output.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.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 freeproccachePosted By - Karan Gupta Posted On - Thursday, April 2, 2015 Query/Feedback Your Email Id Subject Query/Feedback Characters remaining 250
Use Master Go select * from syscacheobjects
Use Master Go dbcc freeproccache
Query/Feedback