Along with colmodctr snapshot values, Query processor sets the Recompile thresholds for every colmodctr created.
Before using an existing cached plan, the Query optimiser performs The threshold crossing test defined by the formula below.

 | colmodctr(current) – colmodctr(snapshot) |  >= RT

current     – refers to the current value of the modification counter
snapshot – refers to the value of the mod. counter captured during the last plan compilation(recompilation).
If the threshold crossing test evaluates to TRUE (the number of changes exceeds the pre-defined RT), for any of the interesting statistics, the query plan is recompiled.
The threshold crossing test will be performed even if the query processor does not load any interesting statistics. In that case, the test will be based simply on table cardinalities.

 | cardinality(current) – cardinality(snapshot) |  >= RT

The following table shows how RT is calculated for permanent and temporary tables

colmodctr is the name of a counter which counts the number of modifications that a table has undergone. The counter is per column and is non-transnational (if we insert 10 rows and rollback the transaction, colmodctr value will increase by 20). The counter is also ever-increasing.

So far, Temp table caching can explain why our query does not get recompiled due to schema changes. Now, let’s find out why our query use stale statistics and why it does not perform optimality-related query recompilation.

Automatically created statistics are cashed along with the cached temp tables. In this case,  auto-created statistics are NOT reset at the end of the stored procedure execution cycle nor at the beginning of a consecutive stored proc. execution.
This is why statistics on a cached temporary table may belong to one of the previous stored procedure calls and are absolutely not related to the context of the current temp table.
If we used a “normal”(dbo.Temp) table instead of the #temp table, the table would be dropped and recreated on each stored procedure call. Consequently, auto-created statistics would be repeatedly dropped and created which would make them always up-to-date.

The modification counters which count changes on the interesting columns, in our example columns: ProductName and ProductId will not cause query recompiles even if the number of the changes passes the RT threshold test. This behavior is unique to the temp tables(Cached or non-cached *) when used within stored procedures.

Note*: To prevent temp table caching, perform a DDL operation on the newly created temp table. e.g

...
    CREATE TABLE #temp( 
             ProductId INTEGER)
    ALTER TABLE #temp
        ADD ProductName NVARCHAR(256)
..
/* -- this change will cause Err 2767, Could not locate statistics 'ProductName' in the system catalogs
   -- starting from the second execution
 DBCC SHOW_STATISTICS ('tempdb.dbo.#Temp', ProductName) 
             WITH STAT_HEADER
                 ,HISTOGRAM;

*/

Err 2667: The statistics will be internally present, but not available through sp_helpstats, DBCC SHOW_STATISTICS, and tempdb.sys.stats. My assumption would be that the auto-crated stats. BLOB entry gets “disconnected” from temp table’s object_id -> For non-cached temp tables, the object_id changes on every sp run.
What is also interesting with the non-cached table is that it does not force our query to be recompiled due to schema change reasons as we might expect.

Back to the original example, to be able to track the modification counters, expand dbo.testTempTableStats stored procedure with the dynamic query below.

--tSQL 2017 syntax
CREATE OR ALTER PROCEDURE dbo.testTempTableStats(
    @StartsWith NVARCHAR(5)
)
AS
BEGIN
    CREATE TABLE #temp( 
            ProductId INTEGER
            ,ProductName NVARCHAR(256)
    )
    
    INSERT INTO #temp
        SELECT pr.ProductID
               ,pr.[Name]
        FROM dbo.Products pr
        WHERE pr.[Name] LIKE @StartsWith + '%'                 
 
        SELECT t.ProductName
              ,NoOfOrders = COUNT(DISTINCT tr.OrderId) 
        FROM dbo.Transactions tr
            INNER JOIN #temp t
                ON tr.ProductId = t.ProductId
        GROUP BY t.ProductName

    ---sys.dm_db_stats_properties works only of the current db context :(
    EXECUTE( N'USE tempdb; 
                  SELECT  [Object] = object_name(s.object_id)
                            ,s.[name]
                            ,sp.last_updated
                            ,sp.[rows]
                            ,s.auto_created
                            ,sp.modification_counter 
                  FROM sys.stats s 
                  CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
                  WHERE s.object_id = object_id(''#temp'',''U'');'
    );

    -- Show automatically created statistics for the column "ProductName"
    DBCC SHOW_STATISTICS ('tempdb.dbo.#Temp', ProductName) 
         WITH STAT_HEADER
             ,HISTOGRAM;
 
    DROP TABLE #temp;
END

Now execute the query with the parameter ‘C’

DBCC FREEPROCCACHE
GO
EXEC testTempTableStats
        @StartsWith = N'C';
GO

Figure 7, colmodctr – modification counters

On the first query execution, the query optimiser sets up the RT values for the two auto-created columns. The Recompilation thresholds are based on the temp table’s cardinality (NoOfRows = 10). Based on the previously mentioned formula ( 6 < n < 500, RT = 500 ), in order to pass the RT crossing test we’ll need at least 510(500+10) changes on either of the two columns to initiate query recompilation. This means that, if we execute stored proc 6 times passing parameter ‘A‘ (populates temp table with 100 rows), we will reach 6*100 = 600 changes which are more than we need to pass the test.

EXEC dbo.testTempTableStats 
             @StartsWith = 'A'
GO 6

The 6th execution shows the following results, but no expected recompile.

Figure 8, colmodctr – modification counters do not initiate query recompiles

The table below shows colmodctr counter’s progress:

Figure 9, modification counter progress

As we can see, the query was not recompiled due to optimality reasons. Non-cached temp tables will show the same behavior. The colmodctr simply does not work for temp tables within stored procedures.
If we repeat the experiment, but this time we pass parameter ‘B'(param ‘B’ results in 4 rows in the temp table) for the very first stored procedure call, we will get a similar output as in Figure 7. RT value is now set to 6,  1<n<6, RT = 6, which means that we need at least 10(6+4) changes on the interesting temp table columns to initiate query recompilation.
If we execute the same stored procedure(with param ‘B’), say two more times, the modification counters will show 16 changes and, as expected, with no recompile.
But if we execute the stored procedure with a parameter that generates 10 or more rows in the temp table, i.e ‘C’  or ‘E’ (10 and 27 rows) our query will recompile.

DBCC FREEPROCCACHE
GO
-- this will set up RT = 10
EXEC testTempTableStats 
        @StartsWith = 'B'
GO
-- modification counter does not initiate recompiles
EXEC testTempTableStats 
    @StartsWith = 'B'
GO 2 -- no recompiles

DBCC FREEPROCCACHE
GO
-- this will set up RT = 10
EXEC testTempTableStats 
        @StartsWith = 'B'
GO
--  a parameter that generates 10 or more rows in temp table do initiate query recompile
EXEC testTempTableStats 
    @StartsWith = 'C' -- 'E', [A-Z],'A' ...

The experiment shows that the Query processor does track temp table cardinality changes.
The threshold crossing test will be based on temp table cardinality.

 | cardinality(current) – cardinality(snapshot) |  >= RT

In our case RT =6, the expected current table cardinality is 10, | 10 – 4 | >=6, and the  RT crossing test evaluates to TRUE.

The interesting thing about this is if our code uses a temp table that has a relatively stable number of rows i.e ~500, it is likely that we’ll end up with one plan that will never change due to optimality reasons. If our sp creates a query plan for @StartsWith = ‘C’ parameter that sets up RT=500, the query optimizer will expect the temp table’s cardinality to increase to at least 510 rows, which will never happen if our table has a maximum of 500 rows. The difference will be more visible i.e in the case when the Query optimizer sets RT to 500 +20%*n (total number of rows – see the RT tables above). The expected cardinality that will initiate recompile will be n + 500 +0.2*n.

Possible solutions

WITH RECOMPILE

One of the obvious solutions for this problem would be to create a stored procedure WITH RECOMPILE. This will

• Prevent temp table caching
• Prevent stored procedure query plan caching
• Recompile all queries in the stored procedure on each execution.

--tSQL 2017 syntax
CREATE OR ALTER PROCEDURE dbo.testTempTableStats(
    @StartsWith NVARCHAR(5)
)
WITH RECOMPLILE
AS
BEGIN
..

This method may work out of the box, but may also be expensive since the whole batch that can contain quite a few complex queries must compile on each call, and then, during the execution, separate queries may be recompiled due to previously mentioned reasons.

EXECUTE .. WITH RECOMPILE

In situation, we know that the cached query plan works for the most common range of input parameters. If we are able to identify those parameters for which the plan would be sub-optimal, we can create a wrapper stored procedure that controls the main stored procedure execution properties.

--wrapper stored procedure
CREATE OR ALTER PROCEDURE testTempTableStatsWrapper(
      @StartsWith NVARCHAR(5)
)
AS
BEGIN
    SET NOCOUNT ON;

    IF @StartsWith IN ('Z','B','C','E')
        EXECUTE testTempTableStats
                @StartsWith
    ELSE  
        EXECUTE testTempTableStats
                @StartsWith
            WITH RECOMPILE
    RETURN;
END

A stable plan will be cached for the parameters Z, B, C, and E, and a second, non-cached plan will be constructed each time we pass any other parameter value than the four mentioned above.

OPTION RECOMPILE and UPDATE STATISTICS

To overcome the problem of stale statistics, we can add OPTION RECOMPILE to our original query. This will force our query to recompile.

--tSQL 2017 syntax
CREATE OR ALTER PROCEDURE dbo.testTempTableStats(
    @StartsWith NVARCHAR(5)
)
AS
BEGIN
    ..
        SELECT t.ProductName
              ,NoOfOrders = COUNT(DISTINCT tr.OrderId) 
        FROM dbo.Transactions tr
            INNER JOIN #temp t
                ON tr.ProductId = t.ProductId
        GROUP BY t.ProductName
        OPTION (RECOMPILE);
...

 

If we repeat the first test and pass parameters ‘C’ and ‘F’ respectively, we can see that the “F” param‘s option recompile updates only the temp table’s cardinality, from 10 rows to 358 rows. The process skips updating auto-created statistics on ProductName and ProductId.

DBCC FREEPROCCACHE; 
GO
EXEC dbo.testTempTableStats @StartsWith = 'C'
GO
EXEC dbo.testTempTableStats @StartsWith = 'F'
GO

Figure 10, Stale statistics, correct cardinality

OPTION(RECOMPILE) forces the query to recompile, but it does not detect stale statistics. It also “activates” rowmodcnt (modification_counter). In this case, after the first execution(param ‘C’), RT is set to 500. QO needs to detect at least 510 changes on the significant columns to initiate the recompile. In our case, when the modification counter reaches 510+ changes(Two consecutive calls passing param. “A”), including temp table truncates,  QO will update statistics as a part of the query recompilation.

1st Execution Param “C” : RowNo=10: RT=500; modification_counter = 0
2nd Execution Param “A” : RowNo=358: RT=500; modification_counter = 10(truncate) + 358 = 368
3rd Execution Param “A” : RowNo=358: RT=500; modification_counter = 358(Truncate) +  368 = 726** RT crossing test evaluates to TRUE -> Update Statistics.

For non-cached temp tables, OPTION RECOMPILE does update auto-created statistics as well.

We can force the query optimizer to update auto-created statistics using UPDATE STATISTICS  #temp. The complete solution would be …

--tSQL 2017 syntax
CREATE OR ALTER PROCEDURE dbo.testTempTableStats(
    @StartsWith NVARCHAR(5)
)
AS
BEGIN
    CREATE TABLE #temp( 
            ProductId INTEGER
            ,ProductName NVARCHAR(256)
    )
    
    INSERT INTO #temp
        SELECT pr.ProductID
               ,pr.[Name]
        FROM dbo.Products pr
        WHERE pr.[Name] LIKE @StartsWith + '%'                 
 
        UPDATE STATISTICS #temp;        

        SELECT t.ProductName
              ,NoOfOrders = COUNT(DISTINCT tr.OrderId) 
        FROM dbo.Transactions tr
            INNER JOIN #temp t
                ON tr.ProductId = t.ProductId
        GROUP BY t.ProductName
        OPTION(RECOMPILE);
 
    ---sys.dm_db_stats_properties works only off the current db context :(
    EXECUTE( N'USE tempdb; 
                  SELECT  [Object] = object_name(s.object_id)
                            ,s.[name]
                            ,sp.last_updated
                            ,sp.[rows]
                            ,s.auto_created
                            ,sp.modification_counter 
                  FROM sys.stats s 
                  CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
                  WHERE s.object_id = object_id(''#temp'',''U'');'
    );
 
    -- Show automatically created statistics for the column "ProductName"
    DBCC SHOW_STATISTICS ('tempdb.dbo.#Temp', ProductName) 
         WITH STAT_HEADER
             ,HISTOGRAM;
 
    DROP TABLE #temp;
END

It is a bit unusual place to put the UPDATE STATISTICS statement since we expect auto-created statistics to be constructed during our query compilation/recompilation process, not before. But then, if we put the statement after the query, UPDATE STATISTICS will not be able to have an effect on the preceding query.

BATCH MODE MEMORY GRANT FEEDBACK (SQL Server 2017+)

This solution is just an experimental one. In SQL Server 2017 Microsoft introduced Adaptive Query Processing, a few new query processing features set to improve query performance.  The new features (v1.0) are

• Batch mode memory grant feedback
• Batch mode adaptive joins and
• Interleaved execution

More about this interesting set of features can be found here.
The first two features are available in queries that reference one or more objects with columnstore indexes. Opposed to row-mode execution style, when rows are passed/processed between iterators(operators) one at a time, the batch processing operates on a 64bytes structure(8 pages). The allocated memory space can host between 64 and 900 rows depending on the number of columns selected.
To activate the features(and the batch processing) we can create a “dummy” columnstore index with an int column and LEFT JOIN it with our query. The new join will not change the query logic nor the result-set.

--Create a dummy columnstore index 
DROP TABLE IF EXISTS dbo.dummyColumnstore;
GO
CREATE TABLE dbo.dummyColumnstore(i int)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX nc_csi_id 
    ON dbo.dummyColumnstore(i)
GO

--add an extra LEFT JOIN to the original query
CREATE OR ALTER PROCEDURE dbo.testTempTableStats(
    @StartsWith NVARCHAR(5)
)
AS
BEGIN

    CREATE TABLE #temp( 
            ProductId INTEGER
            ,ProductName NVARCHAR(256)
    )
    
    INSERT INTO #temp
        SELECT pr.ProductID
               ,pr.[Name]
        FROM dbo.Products pr
        WHERE pr.[Name] LIKE @StartsWith + '%'                 

        SELECT t.ProductName
              ,NoOfOrders = COUNT(DISTINCT tr.OrderId) 
        FROM dbo.Transactions tr
            INNER JOIN #temp t
                ON tr.ProductId = t.ProductId
            LEFT OUTER JOIN dbo.dummyColumnstore dcc -- columnstore idx
                ON tr.ProductId = dcc.i
        GROUP BY t.ProductName;

    DROP TABLE #temp;

    RETURN;
END

If we run the original test again, first with param ‘C’ and then with param ‘F’, we’ll notice a couple of interesting changes in the query execution plan.

DBCC FREEPROCCACHE; 
GO
EXEC dbo.testTempTableStats @StartsWith = 'C'
GO
EXEC dbo.testTempTableStats @StartsWith = 'F'
GO

The first execution creates a new plan

Figure 11, Adaptive Query Processing features

We can notice a new Adaptive Join operator and a new, batch execution mode for Hash Match Aggregate and Compute scalar operators.
The next execution (param ‘F’) will result in underestimated memory grants for the Hash Match Aggregate operator. This is due to stale statistics, as it was shown earlier.
Now, if we execute the same query again(param ‘F’), the Batch mode memory grant feedback feature will FIX the problem with the operator by allocating enough memory for the operation. This will fix the problem with the Aggregate operator and prevent data spills. However, the original problem with the stale statistics, etc… will stay.

Conclusion

Temporary tables when used in stored procedures may have totally wrong statistics. This can lead to performance degradation due to sub-optimal plans including not enough accurate memory grants, wrong temp table cardinality estimates, etc. This is partially due to the temp table caching mechanism.  The feature is extremely useful since it provides more efficient temp table reuse across frequent stored procedure calls, particularly in OLTP workloads.  Between stored proc. calls, the column modification counters on interesting temp table columns do not initiate query recompiles ( even if the RT crossing test evaluates to true).  However, the RT crossing test that depends on the temp table’s cardinality changes, if evaluated to true, will initiate query recompiles.
Forcing the Query optimizer to perform a statement recompile does not resolve the problem. For the cached temp tables, it fixes cardinality estimates but does not update relevant statistics. For non-cached tables, it resolves the problem (everything is up to date). Combining UPDATE STATISTICS #tempTable and OPTION RECOMPILE is one way to workaround the problem.
The example demonstrates a common scenario when a temp table is used to reduce query plan complexity.  The table stores a relatively small number of rows and drives a more complex query. If the cached plan is sensitive to the cardinality and statistical distribution of the values in the temp table, the plan reuse may result in stored procedure performance degradation.
For the larger temp tables with widely differing numbers of rows on every execution, it is likely that the main query recompiles due to temp table cardinality changes.
For the query plans that are less dependent on the temp table statistics and cardinality, we might consider using table variables instead(lightweight, no statistics, cardinality presented as 1row etc.)