SQL Server Procedure Cache and Execution Plans

 Procedure cache and data  cache information  in sql server can play a vital role in understanding the performance of the sql server.These two contains  two core aspects of  sql server performance  namely the query  executions plans and the  data   pages  both   of which are stored in buffer cache

Procedure Cache:
Sql server engine  generates  query execution plans  by taking different factors into consideration such as  existing indexes , tables, joins  etc  and at the end of this process  it produces an execution plan, which it considers as  best for executing the query .This entire process is done by a cost based  allocation approach  in which  it  estimates   percentage  of   cost  of resources  to  process each operation in the query and generates an best possible execution plan to execute the query .

Each time  Sql Server  generates an execution plan  it  consumes certain amount of system resources    and to avoid  generating a  new  execution plan  for  same query which has been executed previously   sql server  has a mechanism called  procedure cache ,which  stores these execution plans  in memory pool , so that when  a query is executed  and an existing plan is available in procedure cache, it will use that plan  and helps to save the system resources .The amount of time this query is stored in the procedure cache  is again another process, in which sql server  uses a age based approach ,which essentially means if a plan is re used again , than  it will  keep the execution plan  in the procedure cache   and the plan will live  for longer time in the procedure cache, and if a plan is not used ,than the plan  will be eventually  removed from the procedure cache  as other executions plans get added  and by doing this,  it uses buffer cache efficiently .

Understanding Query execution plan is very important to query design and indexing ,sql server management studio enables to view estimated execution plan and actual execution plan as shown below

The execution plan in the above diagram  contains number of operators, to show what actually is happening  when sql server is trying to build the execution plan for the query  .Each operator has a associated cost with it , like in this case the cost of clustered index scan  is 100% as  shown  in the above diagram ,I will change the query slightly  and we will observer how the new execution plan looks like

In this  second query as shown  in the below diagram  i have added a  filter  on geography key and the execution plan  changed  considerable   as shown as below

As you can see when i added a filter , the execution plan changed  and now there is a index seek operation due to the fact that there is a non clustered index on the geography id filter column   and naturally the execution plan determined that the  index  seek is is more cost efficient operation due to the fact fact that it fetches few records when compared to a index scan and also you can see ,there is a  Key Look Up  operator in the execution plan which is essentially used to look up the records from the non clustered index to the clustered index and has an associated cost of 50%  and also  there is a Nested loop join which is the result of  key loop operator.Explaining  each operator in execution plan is beyond the scope of this post  and the above examples were used to show, what is an execution plan, and how it is relevant  in query building



Usefull   DMV   to get  Procedure  Cache   information in Sql Server

select s.usecounts as execcounts , s.size_in_bytes,s.cacheobjtype,  t.text ,db_name(st.database_id) as DatabaseName,st.total_elapsed_time   from sys.dm_exec_cached_plans  s
inner join  sys.dm_exec_procedure_stats  st on  s.plan_handle=st.plan_handle
cross apply sys.dm_exec_sql_text(s.plan_handle) t


UseFull  DBCC command to clear Procedure cache in sql server

DBCC FREEPROCCACHE