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()
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.
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.