KEEP PLAN Hint: Cut Temp Table Recompiles in SQL Server

The KEEP PLAN hint tells SQL Server to wait longer before it recompiles a statement that reads a temp table. It matters for procedures that fill a temp table row by row and read it between the inserts. The effect is narrow, and a short test shows exactly where it starts and where it stops.

Why Temp Tables Recompile Early

A query plan depends on statistics. SQL Server recompiles a statement when enough rows of a table have changed. The old plan can stop fitting the data. The number of changes that triggers it is the recompile threshold. For a permanent table the threshold is 500 changes when the table holds up to 500 rows. A temp table with fewer than 6 rows has a threshold of only 6.

A procedure that starts with an empty temp table and adds rows crosses that low threshold at once. Each statement that reads the table then recompiles. In one tuning engagement, a long stored procedure used one temp table over and over. In the client’s report, the repeated recompiles slowed it down, and the KEEP PLAN hint brought it under control. Recompilation works at the statement level in current versions, so each recompile is small. A long procedure can pay it many times.

Build the Demo

The demo database KeepPlanDemo holds a table of 20,000 items. Three procedures do the same work. Each creates a temp table and adds rows to it in a loop. After every round it joins the table to the items. The first procedure has no hint. The second uses KEEP PLAN and the third uses KEEPFIXED PLAN. Two parameters set the number of rounds and the rows added per round.

IF DB_ID(N'KeepPlanDemo') IS NULL CREATE DATABASE KeepPlanDemo;
GO
USE KeepPlanDemo;
GO
DROP TABLE IF EXISTS dbo.Items;
CREATE TABLE dbo.Items (ItemID int NOT NULL PRIMARY KEY, Price decimal(9,2) NOT NULL);
INSERT INTO dbo.Items (ItemID, Price)
SELECT TOP (20000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 5.00
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
GO
CREATE OR ALTER PROCEDURE dbo.LoadPlain @Rounds int, @RowsPerRound int AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #Work (ItemID int NOT NULL PRIMARY KEY);
    DECLARE @i int = 1, @total decimal(12,2);
    WHILE @i <= @Rounds
    BEGIN
        INSERT INTO #Work (ItemID)
        SELECT ItemID FROM dbo.Items WHERE ItemID > (@i - 1) * @RowsPerRound AND ItemID <= @i * @RowsPerRound;
        SELECT @total = SUM(i.Price) FROM dbo.Items AS i INNER JOIN #Work AS w ON w.ItemID = i.ItemID;
        SET @i += 1;
    END;
END;
GO
CREATE OR ALTER PROCEDURE dbo.LoadKeep @Rounds int, @RowsPerRound int AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #Work (ItemID int NOT NULL PRIMARY KEY);
    DECLARE @i int = 1, @total decimal(12,2);
    WHILE @i <= @Rounds
    BEGIN
        INSERT INTO #Work (ItemID)
        SELECT ItemID FROM dbo.Items WHERE ItemID > (@i - 1) * @RowsPerRound AND ItemID <= @i * @RowsPerRound;
        SELECT @total = SUM(i.Price) FROM dbo.Items AS i INNER JOIN #Work AS w ON w.ItemID = i.ItemID OPTION (KEEP PLAN);
        SET @i += 1;
    END;
END;
GO
CREATE OR ALTER PROCEDURE dbo.LoadKeepFixed @Rounds int, @RowsPerRound int AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #Work (ItemID int NOT NULL PRIMARY KEY);
    DECLARE @i int = 1, @total decimal(12,2);
    WHILE @i <= @Rounds
    BEGIN
        INSERT INTO #Work (ItemID)
        SELECT ItemID FROM dbo.Items WHERE ItemID > (@i - 1) * @RowsPerRound AND ItemID <= @i * @RowsPerRound;
        SELECT @total = SUM(i.Price) FROM dbo.Items AS i INNER JOIN #Work AS w ON w.ItemID = i.ItemID OPTION (KEEPFIXED PLAN);
        SET @i += 1;
    END;
END;
GO

Count the Recompiles With Extended Events

Run this on a test server. The session is a server object, and the last script removes it.

An Extended Events session records every statement recompile in the demo database and keeps the events in memory. The helper procedure reads the events and counts the recompiles caused by changed statistics for each procedure. Then it restarts the session, so the next test starts at zero. Creating the session needs the ALTER ANY EVENT SESSION permission, and reading it needs VIEW SERVER STATE. The cleanup at the end drops the session.

USE master;
GO
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'KeepPlanDemoXE') DROP EVENT SESSION KeepPlanDemoXE ON SERVER;
DECLARE @sql nvarchar(max) = N'CREATE EVENT SESSION KeepPlanDemoXE ON SERVER
    ADD EVENT sqlserver.sql_statement_recompile (WHERE (source_database_id = ' + CAST(DB_ID(N'KeepPlanDemo') AS nvarchar(10)) + N'))
    ADD TARGET package0.ring_buffer WITH (MAX_DISPATCH_LATENCY = 1 SECONDS);';
EXEC (@sql);
ALTER EVENT SESSION KeepPlanDemoXE ON SERVER STATE = START;
GO
USE KeepPlanDemo;
GO
CREATE OR ALTER PROCEDURE dbo.ShowRecompiles AS
BEGIN
    SET NOCOUNT ON;
    WAITFOR DELAY '00:00:02';
    DECLARE @x xml = (SELECT CAST(t.target_data AS xml)
                      FROM sys.dm_xe_session_targets AS t
                      INNER JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
                      WHERE s.name = N'KeepPlanDemoXE' AND t.target_name = N'ring_buffer');
    WITH ev AS (
        SELECT n.e.value('(data[@name="object_id"]/value)[1]', 'int') AS ObjectId,
               n.e.value('(data[@name="recompile_cause"]/text)[1]', 'nvarchar(100)') AS Cause
        FROM @x.nodes('//event') AS n(e)
    )
    SELECT p.ProcedureName, COUNT(ev.ObjectId) AS StatisticsRecompiles
    FROM (VALUES (N'LoadPlain'), (N'LoadKeep'), (N'LoadKeepFixed')) AS p(ProcedureName)
    LEFT JOIN ev ON ev.ObjectId = OBJECT_ID(N'dbo.' + p.ProcedureName) AND ev.Cause = N'Statistics changed'
    GROUP BY p.ProcedureName
    ORDER BY p.ProcedureName;
    ALTER EVENT SESSION KeepPlanDemoXE ON SERVER STATE = STOP;
    ALTER EVENT SESSION KeepPlanDemoXE ON SERVER STATE = START;
END;

The session also records other causes. The helper filters on changed statistics, because the hints control that cause. Every procedure with a temp table also logs a deferred compile. It stays in the log for all three procedures, and no hint removes it.

Test One: Rows Arrive One at a Time

The first test adds one row per round for 40 rounds. The temp table starts empty, so it passes the 6 row threshold early in the loop.

EXEC dbo.LoadPlain @Rounds = 40, @RowsPerRound = 1;
EXEC dbo.LoadKeep @Rounds = 40, @RowsPerRound = 1;
EXEC dbo.LoadKeepFixed @Rounds = 40, @RowsPerRound = 1;
EXEC dbo.ShowRecompiles;

The plain procedure recompiles once, when the temp table crosses 6 rows. The KEEP PLAN procedure never does, because it uses the threshold of a permanent table. That is the whole benefit of the KEEP PLAN hint: it removes the early recompiles of a small temp table.

Test Two: The Table Grows Fast

The second test adds 1,000 rows per round for 10 rounds. The table grows by 1,000 rows a round, so the early rounds cross every threshold. Later rounds recompile less, and that is why 10 rounds show 6 recompiles.

EXEC dbo.LoadPlain @Rounds = 10, @RowsPerRound = 1000;
EXEC dbo.LoadKeep @Rounds = 10, @RowsPerRound = 1000;
EXEC dbo.LoadKeepFixed @Rounds = 10, @RowsPerRound = 1000;
EXEC dbo.ShowRecompiles;

Now KEEP PLAN does nothing. It recompiles as many times as the plain version. The threshold of a permanent table is the one KEEP PLAN uses, and the documentation gives it the same behavior. Only KEEPFIXED PLAN stops the recompiles. That hint tells SQL Server to ignore statistics changes, until a schema change forces a new plan.

The Price of a Fixed Plan

You could argue that KEEPFIXED PLAN is the better hint, since it removes every recompile. It also removes the protection. A plan built for 1,000 rows keeps running when the table holds a million. The join method that suited the small table can crawl on the large one. Use the hint on a statement whose plan stays good at any size. Test it with the largest realistic data.

The hint also hides a symptom. Count the recompiles first, as the demo does, and confirm that they cost something. A recompile that takes a millisecond does not justify a fixed plan.

What to Remember

The KEEP PLAN hint matters only for small temp tables. It replaces the threshold of 6 changes with the 500 of a permanent table. A fast-growing temp table recompiles with or without it. KEEPFIXED PLAN ends the statistics recompiles, and it can leave a stale plan behind. Measure first. When you finish the demo, drop the session and the database.

USE master;
GO
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'KeepPlanDemoXE')
BEGIN
    ALTER EVENT SESSION KeepPlanDemoXE ON SERVER STATE = STOP;
    DROP EVENT SESSION KeepPlanDemoXE ON SERVER;
END;
DROP DATABASE IF EXISTS KeepPlanDemo;

A plan hint is not a speed boost, it is a promise that the plan can stay as it is.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.