Plan cache - part 2 - What gets cached

In general, we can classify queries submitted into three catogories.

They are
1) Adhoc queries -> The ones submitted thro query analyzer or the queries from applications which are not prepared statments ( executed as just statement without setting any parameter ).
2) SQL objects -> calls made to Stored procs, Triggers, UDFs, system and extended procs
3) Prepared Statements -> Sp_executesql with parameters,Prepared statements from applications.

Calls to sql objects and prepared statements are more likely to be cached. Let us see it in detail.Consider the following adhoc query:

DBCC freeproccache -- Just to free the cache before we start the experiments.


SELECT supplier_xid,
       product_xid,
       trans_city,
       qty,
       comments,
       balance_left
FROM   supplier_product_customer
WHERE  trans_city = 'San Franciso'
ORDER  BY supplier_xid DESC 


Let us change the parameter and execute again


SELECT supplier_xid,
       product_xid,
       trans_city,
       qty,
       comments,
       balance_left
FROM   supplier_product_customer
WHERE  trans_city = 'Perth'
ORDER  BY supplier_xid DESC 


To check whether the query was cached or not let me fire the query on DMV cached plans.


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%' 




The usecounts column has a value 1 which indicates clearly that the query was not cached. By default, adhoc queries are cached only when there is a exact match. A hange in parameter, even a change in case or additional space in the query results in sql server not reusing the query plan prepared eariler.

Let us make it a Stored proc and try the query again.

CREATE PROCEDURE Test_plan_caching @city VARCHAR(50)
AS
  SELECT supplier_xid,
         product_xid,
         trans_city,
         qty,
         comments,
         balance_left
  FROM   supplier_product_customer
  WHERE  trans_city = @city
  ORDER  BY supplier_xid DESC

GO

EXEC Test_plan_caching 'San Franciso'

GO

EXEC Test_plan_caching 'Perth'

GO 


A query to DMV cached plan shows that the query plan has been reused.



Now let us try sp_executesql method. Consider the following script


DECLARE @SQLString NVARCHAR(1000)
DECLARE @paramstring NVARCHAR(100)
DECLARE @city VARCHAR(100)

SET @SQLString = 'Select supplier_xid,product_xid,trans_city,qty,comments,balance_left from ' + 'Supplier_Product_Customer where trans_city = @variable order by supplier_xid desc '
SET @paramstring = '@variable varchar(100)'
SET @city = 'San Franciso'

EXEC Sp_executesql
  @SQLString,
  @paramstring,
  @variable = @City

SET @city = 'Perth'

EXEC Sp_executesql
  @SQLString,
  @paramstring,
  @variable = @City 


Note that we have changed the parameters by setting a new value to input variable and executed the same query.


DMV cached plan shows that query is getting reused. Bear in mind that mere usage of sp_executesql without using input parameter doesnt reuse the plan.

Note that the object type column on DMV cached plan indicates the kind of object that has been cached. Proc refers to procedure, Prepared refers to prepared plans created by sp_executesql and by application queries and Adhoc refers to adhoc queries.

There are still a few exception scenarios where a stored procedure plan doesnt get effectively reused.We will explore them more in the next post.