Plan Cache - Part 3 - What gets recompiled

As a continuation of series of posts on Plan caching, let us get started with Part -3
Earlier posts are given below:

Plan caching - part-1 - Introduction
Plan caching - part-2 - What gets cached

Last post explained how stored procedures,prepared statements are cached. But there are a few scenarios where caching doesn't work, and object/query plan in the cache gets recompiled. Recompilation is the process where SQL Server finds that the plan in the cache is no good/invalid and creates a new plan for the query submitted. So lets get started. The following scenarios cause recompilation.

* A change in the structure/definition of the object
If the structure of the stored proc/table(Add/delete column, constraint,
index) has changed since last execution then the plan is obviously
invalid and hence it has to be recompiled.

We are using the same stored procedure test_plan_caching defined in the last post. Consider the following script.


DBCC freeproccache --Clear the cache as usual
EXEC Test_plan_caching 'moscow' --Call the SP

GO

ALTER PROCEDURE [dbo].[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 

--Forcing an alter to the sp, though no logical change has been done 
EXEC Test_plan_caching 'moscow'--Call the SP

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




Alter made on the sp forced the optimizer to recreate the plan.
Similar changes that can cause such recompilations are adding/dropping an index,changing a trigger/proc/view definition etc.

* A change in the connection parameters using SET command .

Changing the following connection parameters using SET keyword can cause recompilation.

ANSI_NULL_DFLT_OFF/ANSI_NULL_DFLT_ON/ANSI_NULLS/ANSI_PADDING/ANSI_WARNINGS
/ARITHABORT/CONCAT_NULL_YIELDS_NULL/DATEFIRST/DATEFORMAT/FORCEPLAN/LANGUAGE
/NO_BROWSETABLE/NUMERIC_ROUNDABORT/QUOTED_IDENTIFIER


DBCC freeproccache
GO

EXEC Test_plan_caching 'San Franciso'
GO

SET ansi_null_dflt_off ON
GO

EXEC Test_plan_caching 'San Franciso'
GO 




Query stats DMV clearly shows two plans. One for the plan generated before setting ANSI_NULL_DFLT_OFF and other for the plan with different value on ANSI_NULL_DFLT_OFF.

Note that a change of option 'ANSI_NULL/QUOTED_IDENTIFIER' doesn't affect a Stored proc or Table valued function. But it does affect Adhoc queries or prepared statements.

* A Forced recompilation

When a Object is marked for recompilation using sp_recompile stored procedure or when with recompile hint is used then query is recompiled.
Example:

EXEC Sp_recompile 'test_plan_caching' 


The statement above ensures that next call made to test_plan_caching results in a recomplie.

When a procedure is created with an option WITH RECOMPILE, any call made to the stored proc
is always recompiled.

usage of hint OPTION RECOMPILE causes a statement level recompilation.
The scenarios where these options are useful are potential posts on their own. Will cover them soon.

* A change in statistics, caused by changes done to most of the rows in the table.
A rough algorithm of how SQL Server decides whether the table needs an updated statistics is given below. The algorithm changes is based on RT factor ( Recompilation threshold ) which depends on the number of changes made to the table.
If a table contains less than 500 rows then atleast 500 changes are required to cause a recompilation.If a table has more than 500 rows, then number of changes required are 500 + 20 % of rowcount.

As the algorithm changes with version and service pack releases, its approximate.