Aside from explicitly flushing the cache or demanding recompiles, what recompiles a full stored procedure in SQL Server 2019?

Question

I recently finished reading Plan Caching in SQL Server 2008 and I have become confused. It seems that, short of totally flushing the plan cache or explicitly demanding a stored procedure to be recompiled, recompilations of stored procedures from SQL Server 2008 onwards are done at the statement level rather than the stored-procedure level.

So, aside from explicitly flushing the cache or demanding recompiles (e.g. WITH RECOMPILE), what recompiles a full stored procedure in SQL Server 2019 rather than just recompiling individual statements?

To give an example of where I am confused, consider the following procedure.

CREATE PROCEDURE FOO AS
BEGIN
   SELECT * INTO #temp1 FROM table1
   INSERT BAR1 SELECT * FROM #temp1
   INSERT BAR2 SELECT * FROM #temp1
END

I can think of plenty of things that could cause SELECT * INTO #temp1 FROM table1 to recompile, but it would be strange for that to recompile without the next lines also recompiling. This makes me think that there must be some things within SQL Server that will cause entire stored procedures to recompile.

Answer 1

not much

Outside of what has been listed in your question, there is really no common generic cause for a stored procedure to always recompile a new plan for every statement, at least not for the one in your example.

SQL Server offers a couple different ways to track recompiles, but they are all statement-level. Probably the easiest to get answers from is Extended Events. There is no Event for tracking recompilation at the object level, that I've come across.

SELECT
     d.map_value
FROM sys.dm_xe_map_values AS d
WHERE d.name = 'statement_recompile_cause'
ORDER BY
    d.map_key;

As of SQL Server 2022, this lists 23 reasons for recompilation, including, uh "Not a recompile".

• Schema changed
• Statistics changed
• Deferred compile
• Set option change
• Temp table changed
• Remote rowset changed
• For browse permissions changed
• Query notification environment changed
• PartitionView changed
• Cursor options changed
• Option (recompile) requested
• Parameterized plan flushed
• Test plan linearization
• Plan affecting database version changed
• Query Store plan forcing policy changed
• Query Store plan forcing failed
• Query Store missing the plan
• Interleaved execution required recompilation
• Not a recompile
• Query Store hints changed
• Query Store hints application failed
• Query Store recompiling to capture cursor query
• Recompiling to clean up the multiplan dispatcher plan

however

There are a couple things I'm aware of that prevent plan caching, which would make it seem like a module needs to recompile every time (though in reality it's more like just compiling every time, I feel).

1. Symmetric keys

As Paul notes in his post:

The second option is to include a statement that marks the entire batch as uncacheable. Suitable statements are often security-related or otherwise sensitive in some way.

This might sound impractical, but there are a couple of mitigations. First, the sensitive statement need not be executed—it just needs to be present. When that condition is met, the user running the batch doesn’t even need permission to execute the sensitive statement. Note carefully, the effect is confined to the batch containing the sensitive statement.

Follow the link for a couple examples.

2. Non-existent objects

I don't have a post published about this yet, but the general idea is that when there is an unexplored IF branch in a stored procedure that references an object that doesn't exist, the entire batch is uncacheable.

Feel free to follow along with the below demo. Note that if you try to get an estimated plan for the stored procedure as written below, or if you run it in a way that explores the branch with the non-existent object, it will throw an error.

/*Create a table if you need to*/
CREATE TABLE 
    dbo.DinnerPlans
(
    id bigint IDENTITY,
    name nvarchar(40) NOT NULL,
    seat_number tinyint NULL,
    is_free bit NOT NULL,
);
GO

/*First example, with an object that doesn't exist*/
CREATE OR ALTER PROCEDURE
    dbo.i_live
(
    @decider bit = NULL
)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    
    IF @decider = 'true'
    BEGIN
        SELECT
            dp.*
        FROM dbo.DinnerPlans AS dp;
    END;
    
    IF @decider = 'false'
    BEGIN
        SELECT
            whatever.*
        FROM dbo.AnObjectThatDoesntEvenPretendToExist AS whatever;
    END;
    
    IF @decider IS NULL
    BEGIN
        SELECT
            result = 
                'please make a decision.'
    END;
END;
GO 

/*Say goodbye!*/
DBCC FREEPROCCACHE;

/*This runs without an error*/
EXEC dbo.i_live 
    @decider = 'true';

/*But there's no query plan!*/
SELECT
    object_name =
       OBJECT_NAME(deps.object_id, deps.database_id),   
    deps.type_desc,
    deps.last_execution_time,
    deps.execution_count,
    dest.text,
    query_plan =
        TRY_CAST(detqp.query_plan AS xml)
FROM sys.dm_exec_procedure_stats AS deps
OUTER APPLY sys.dm_exec_sql_text(deps.plan_handle) AS dest
OUTER APPLY sys.dm_exec_text_query_plan(deps.plan_handle, 0, -1) AS detqp;
GO 

Answer 2

This makes me think that there must be some things within SQL Server that will cause entire stored procedures to recompile.

Sure, you can explicitly define the stored procedure to recompile every time it runs, with the recompile option like so:

CREATE PROCEDURE FOO
WITH RECOMPILE
AS
BEGIN
   SELECT * INTO #temp1 FROM table1
   INSERT BAR1 SELECT * FROM #temp1
   INSERT BAR2 SELECT * FROM #temp1
END

Alternative options are also executing the stored procedure with the recompile hint:

EXEC FOO WITH RECOMPILE;

Or explicitly calling the system procedure sp_recompile:

EXEC sp_recompile N'dbo.FOO';