Plan cache - Introduction

When a query is submitted to SQL server, the query optimiser checks the syntax, verifies the objects and prepares a query plan. A query plan prepared is stored in a area of memory called plan cache . A plan placed in plan cache will be reused when a same query or 'similar query' is submitted to the query analyzer. Preparing a plan everytime can be expensive and time consuming task
and hence the query optimiser attempts to resuse a plan when it is supposed to.

A quick example.

DECLARE @dt DATETIME

SET @dt = Getdate()

SELECT supplier_xid,
       product_xid,
       trans_city,
       qty,
       comments,
       balance_left
FROM   supplier_product_customer
WHERE  trans_city IN( 'San Franciso', 'Perth' )
ORDER  BY supplier_xid DESC

SELECT Datediff(ms, @dt, Getdate()) 

First, the above query is executed. The table contains about 110,000 rows and the query returns about 45000 rows.The query returned the results first time in 1217 ms.

Let us execute the same script again. The script completes in 626 ms which is about half of first execution's time. When executing for the second time,The table's query plan is already in the cache and hence it is reused. We can check whether our query is cached or not using the dmv sys.dm_exec_cached_plans. The following query helps us find the same.

SELECT TEXT,
       usecounts,
       size_in_bytes,
       cacheobjtype,
       objtype
FROM   sys.dm_exec_cached_plans
       OUTER APPLY sys.Dm_exec_sql_text(plan_handle) AS sqltext
WHERE  TEXT NOT LIKE '%dm_exec_cached_plans%' 





Observe the result provided above. The text column provides the query and usercounts indicates how many times the plan has been used. For the above script, usecounts value is 2 and hence it indicates that the query has been reused.

In general, the queries that are 'likely' to be cached and used are as follows.

* Stored procedures, Triggers, Table valued functions.
* Prepared Statements used from applications ( from java/.net/c# etc )
* Queries executed using sp_executesql with input paramters.

As always there are a few exceptions and thats the reason the word 'likely' is in place.

What is written here is just the tip of the iceberg and we will explore more and more on the caching, pros and cons of caching,and the way plan cache works in the next few posts.