TempDB caching issues with SQL Server 2019

Viewed 367

I have a problem that works in SQL Server 2017 but not in SQL Server 2019. It is related to tempdb caching. This has to do with creating temporary tables in stored procedures and changing its structure using dynamic SQL. We have a need to do that for various dynamic reporting needs. The first time it is called, the structure is cached and subsequent call to the procedure fails or returns invalid results. How do I prevent caching of such tables? Below is some sample code and how come it works in 2017. Help appreciated.

CREATE PROCEDURE [dbo].[tempDBCachingCheck]
    @yearList varchar(max)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @yearCount int 
    DECLARE @yearCounter INT 
    DECLARE @yearValue INT 

    DECLARE @sql nvarchar(max) 

    -- With table variable 
    DECLARE @tempYearList TABLE (id INT IDENTITY(1,1), rpt_yr int)
    INSERT INTO @tempYearList (rpt_yr)

    SELECT value FROM STRING_SPLIT(@yearList, ',');                                                                                                                                                                                                                    
                                                               
    SELECT * FROM @tempYearList

    --------------------------------------------------------------------
    --With temporary table, since we will be altering this with dynamic sql 
    CREATE TABLE #returnTable (id INT IDENTITY(1,1))
    
    -- Tried adding a named constraint to not make it cache, but does not work 
    ALTER TABLE #returnTable
    ADD CONSTRAINT UC_ID UNIQUE (id);
        
    SELECT @yearCount = COUNT(*) FROM @tempYearList 
        
    -- Add the years as columns to the return table to demostrate the problem 
    SET @sql = N'ALTER TABLE #returnTable ADD ' 
    SET @yearCounter = 1

    WHILE @yearCounter <= @yearCount 
    BEGIN   
        SELECT @yearValue = rpt_yr FROM @tempYearList WHERE id = @yearCounter

        IF @yearCounter > 1
            SET @Sql = @Sql + N', '

        SET @sql = @sql + N' [' + convert(varchar(20), @yearValue) + N'] float' 
        SET @yearCounter = @yearCounter + 1
    END

    EXECUTE sp_executesql @sql 
    
    SELECT * FROM #returnTable

    -- No need to drop the temporary tables but doing just in case 
    DROP TABLE #returnTable 
END 
GO                               

-- run these statements and you will see the second call with return the cached #returnTable
EXEC tempDBCachingCheck '2019,2020' 
EXEC tempDBCachingCheck '2017,2018,2019,2020'
GO 

-- Clear temp table cache and call in reverse order, then will hit an error 
-- 'A severe error occurred on the current command. The results, if any, should be discarded.' 
USE tempDB
GO 
DBCC FREEPROCCACHE
GO 

EXEC tempDBCachingCheck '2017,2018,2019,2020'
EXEC tempDBCachingCheck '2019,2020'
GO 
1 Answers

It seems this has been fixed in one of cummulative update. The description seems to match:

KB4538853:

When you repeatedly run a stored procedure that uses temporary table with indexes on SQL Server 2019, the client may receive an unexpected error with message "A severe error occurred on the current command" and an access violation exception is recorded on the SQL Server. If the same workload is executed on any previous major version of SQL Server, this issue does not occur.

Dan Guzman's recommendation to install newest CU is the way to go.


Using: EXEC tempDBCachingCheck '2017,2018,2019,2020' WITH RECOMPILE could help as well.

Related