Temp Table Statistics: Why a #Table Plan Goes Stale

The procedure loads a different amount of data, but its plan remembers an earlier shape. Checking temp table statistics separates that memory from the rows present now. Inspect both before changing the procedure.

Separate Cached Objects From Cached Plans

I check the statistics inside the procedure when estimates stop matching its intermediate data. A temporary table has statistics. Its lifetime inside a procedure doesn't guarantee a fresh distribution summary for every execution.

SQL Server can cache eligible temporary table objects created in stored procedures. Statistics associated with reused objects require careful inspection. A cached execution plan is another layer of reuse.

Those two mechanisms interact with compilation and automatic statistics updates. They don't mean every second execution will use stale estimates. Thresholds, statement recompilation and table definitions affect the outcome.

A change from ten rows to a large load is a useful test. It isn't proof of a failure before you inspect the actual plan. Automatic updates can respond to that change on your server.

The later return to a small load is useful too. A statistics summary built from the larger population can outlive that population. Keep the sequence of calls in the test notes.

Build the Procedure in a Test Database

The procedure below creates an eligible temporary table with an inline index. The load varies by a supplied parameter. The distribution gives a small selected group within the generated rows.

Use a disposable database and open the actual execution plan in SSMS. The generator requests a chosen number of sample rows. Verify the loaded count rather than presenting it as an observed result.

The procedure exposes statistics properties before its final query. The result includes the last update time, recorded rows and modification counter. Read that beside the load count.

The sys.stats query uses tempdb because the table lives there. The statistics name is obtained from metadata rather than guessed. A missing properties row also deserves investigation.

GO separates the procedure definition from later calls. Keep all calls on the same connection for the focused test. Don't clear a shared production cache to make the example behave.

GO
CREATE PROCEDURE dbo.TempStatisticsDemo @Rows int
AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #Work
    (
        ItemId int NOT NULL,
        GroupId int NOT NULL,
        INDEX IX_Work_Group NONCLUSTERED(GroupId)
    );
    INSERT #Work(ItemId, GroupId)
    SELECT TOP (@Rows) n, CASE WHEN n % 100 = 0 THEN 1 ELSE 2 END
    FROM
    (
        SELECT CONVERT(int, ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id)) AS n
        FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
    ) AS numbers;
    SELECT COUNT_BIG(*) AS LoadedRows FROM #Work;
    SELECT s.name, p.last_updated, p.rows, p.rows_sampled, p.modification_counter
    FROM tempdb.sys.stats AS s
    OUTER APPLY tempdb.sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
    WHERE s.object_id = OBJECT_ID(N'tempdb..#Work');
    SELECT ItemId FROM #Work WHERE GroupId = 1;
END;
GO
EXEC dbo.TempStatisticsDemo @Rows = 10;
EXEC dbo.TempStatisticsDemo @Rows = 100000;
EXEC dbo.TempStatisticsDemo @Rows = 10;

Read Temp Table Statistics at the Statement

Inspect estimated and actual rows on the GroupId access operator. Also inspect the final statement's compilation and statistics information. A stale summary and a reused plan aren't identical findings.

When the estimate stays wrong, compare the recorded statistics rows with LoadedRows. On my SQL Server 2025 test, the large call still showed statistics recorded from the earlier ten-row load. A recorded larger population during a small call explains one possible mismatch. The stored histogram adds the distribution detail.

An automatic update during the test is useful evidence too. It shows the threshold and compilation behavior acted on that sequence. Don't remove that behavior from the explanation to force a dramatic demonstration.

The statement reads statistics only when the optimizer needs them. Merely selecting the properties doesn't require a distribution refresh. The inspection itself shouldn't be mistaken for the fix.

What row distribution does the failing application call load? Total rows alone cannot answer that. Two equally sized loads can have different selectivity for GroupId.

Refresh Temp Table Statistics Inside the Procedure

A targeted UPDATE STATISTICS after loading makes the summary describe the current population. The definition applies it while the table exists. An outer window cannot access another session's local temporary table.

The next complete definition adds that refresh after loading. Test the modified procedure through the same call sequence. Compare the properties and actual estimate again.

FULLSCAN reads the populated table to build the summary. That work has a cost. Measure the complete procedure rather than the final query alone.

I use an explicit refresh when the intermediate distribution drives an important join or filter. I don't add FULLSCAN to every temporary table automatically. A tiny intermediate result and a large staging set need different choices.

Name the relevant statistics or index when narrowing the refresh. Refreshing every object on a complex temporary table can add unnecessary work. Keep the fix proportional to the statement's need.

GO
CREATE OR ALTER PROCEDURE dbo.TempStatisticsDemo @Rows int
AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #Work
    (
        ItemId int NOT NULL,
        GroupId int NOT NULL,
        INDEX IX_Work_Group NONCLUSTERED(GroupId)
    );
    INSERT #Work(ItemId, GroupId)
    SELECT TOP (@Rows) n, CASE WHEN n % 100 = 0 THEN 1 ELSE 2 END
    FROM
    (
        SELECT CONVERT(int, ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id)) AS n
        FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
    ) AS numbers;
    UPDATE STATISTICS #Work IX_Work_Group WITH FULLSCAN;
    SELECT ItemId FROM #Work WHERE GroupId = 1;
END;
GO
EXEC dbo.TempStatisticsDemo @Rows = 100000;
EXEC dbo.TempStatisticsDemo @Rows = 10;

Recompile the Statement That Depends on the Load

OPTION (RECOMPILE) compiles the statement for the current execution. It can improve decisions based on current parameters and available statistics. It isn't a universal substitute for refreshing a misleading histogram.

The next complete definition recompiles the final query. Apply this version after recording the previous test. Compare compilation work and execution work across representative calls.

Statement-level recompilation is narrower than recompiling the entire procedure. That distinction matters when other statements reuse good plans. Target the statement whose decisions depend on the changing population.

For strongly changing distributions, test an explicit statistics refresh together with recompilation. Use evidence to decide whether both are necessary. Don't assume one keyword settles every source of stale knowledge.

Compilation CPU belongs in the comparison. A frequently called procedure can exchange execution savings for additional compile demand. The workload's call frequency determines whether that exchange is worthwhile.

GO
CREATE OR ALTER PROCEDURE dbo.TempStatisticsDemo @Rows int
AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #Work
    (
        ItemId int NOT NULL,
        GroupId int NOT NULL,
        INDEX IX_Work_Group NONCLUSTERED(GroupId)
    );
    INSERT #Work(ItemId, GroupId)
    SELECT TOP (@Rows) n, CASE WHEN n % 100 = 0 THEN 1 ELSE 2 END
    FROM
    (
        SELECT CONVERT(int, ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id)) AS n
        FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
    ) AS numbers;
    SELECT ItemId FROM #Work WHERE GroupId = 1 OPTION (RECOMPILE);
END;
GO
EXEC dbo.TempStatisticsDemo @Rows = 100000;
EXEC dbo.TempStatisticsDemo @Rows = 10;

Consider Creating the Index After Loading

Creating the index after the load builds index statistics from the current rows. It can give the consuming statement a fresh distribution. It also adds index construction work to each call.

Post-creation DDL can affect temporary object caching eligibility. That is another cost to evaluate. Don't describe this approach as preserving every reuse benefit of the inline definition.

Test the full load, index build and consuming query together. Compare the same parameters and distributions. Also observe tempdb activity under concurrent procedure calls.

I prefer the smallest measured fix that restores useful estimates. A targeted refresh is easier to explain than several unrelated hints. Recheck it after data distribution changes.

Temp table statistics deserve their own evidence in a procedure investigation. Save the call sequence and the matching actual plans. Then choose a fix for the layer that was stale.

Review temp table statistics only after identifying the exact loaded population for that call. A different input size does not automatically mean the statistics are outdated. Compare the estimates, update timestamps, and modification counters together.

Related reading on this blog: Table Variables, Temp Tables and Parallel Queries and Understanding WITH RECOMPILE in Stored Procedures.

A temporary table is not automatically fresh knowledge, it is data whose statistics and plan still need inspection.

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