Recently I came across an odd problem with a query running on MSSQL 2019 Std involving # tables.

hnbright

Hi Brent,
Hope you are doing well.
Recently I came across an odd problem with a query running on MSSQL 2019 Std. that I did not face with any earlier version of SQL. It seems very basic, and I feel a bit embarrassed to ask, but I want to better understand the nature of the problem and how the online solution works.

The problem query
create procedure stp_myproc1(
    @p_param1 varchar(50),
    @p_param2 bit = 0
)
as
begin
    ...

    CREATE TABLE #_temp_mytable(
        col1 INT NOT NULL,
        col2 varchar(50) NOT NULL,
        col3 varchar(20) NOT NULL,
        PRIMARY KEY(col1, col2, col3)
    )

    insert into #_temp_mytable(col1, col2, col3)
        select ...
    ...

IF OBJECT_ID(N'tempdb..#_temp_mytable’) IS NOT NULL
    BEGIN
        DROP TABLE #_temp_mytable
    END

End


The problem:
This procedure is called by an API (build it .net core) by several users to search for a table for different type of requests. However, most of the time, more than one user seemed to get data which was processed by a different user, hence creating nightmare for me to figure out as we were not using any kind of caching at any level.

Upon searching for over the internet, I came across an article which mentions to change the code something like below. According to the article my previous query will cache the data in the temp table even if I dropped it from using the query, however using the new query below, will not cache the data in the table and will keep the tables separate for each user session.

I believe that earlier, #tables when used in proc had a scope of the same proc and were accessible to only that particular session, and even when the same proc was called by a different session, he will get a different #table to work with. But with this recent encounter, I am pretty confused as to why was this changed, or am I working something incorrectly.

The solution which was proposed by the article
 create procedure stp_myproc1(
    @p_param1 varchar(50),
    @p_param2 bit = 0
)
WITH RECOMPILE
as
begin
    ...

    CREATE TABLE #_temp_mytable(
        col1 INT NOT NULL,
        col2 varchar(50) NOT NULL,
        col3 varchar(20) NOT NULL
    )

ALTER TABLE #_temp_mytable  ADD primary key clustered (col1, col2, col3)

    insert into #_temp_mytable(col1, col2, col3)
        select ...
    ...

IF OBJECT_ID(N'tempdb..#_temp_mytable’) IS NOT NULL
    BEGIN
        DROP TABLE #_temp_mytable
    END

End

Jon_Kirkwood

Hi hnbright

 

Thank you for reaching out on the Redgate forums regarding your SQL question.

I note this isn't Redgate specific, but I have some information that may point you in a direction of research to solve your concern.

 

From your notes and testing, it does appear to be linked to a change in how query plans are cached in SQL Server 2019.

Your understanding of temp table scope is overall correct; it’s the caching and plan reuse change in SQL Server 2019 that introduced this odd behaviour.

Normally, temporary tables (e.g., <strong>#_temp_mytable</strong>) should be session-specific, so each user session calling the stored procedure should get a unique temporary table.

However, under certain conditions, SQL Server can incorrectly reuse execution plans across sessions, especially when temporary tables and plan caching interact in ways that might lead to “temp table reuse” between sessions. I believe this is what you're seeing in your nightmare scenario.

 

In SQL Server 2019, changes in the query optimizer’s behaviour can occasionally cause SQL Server to mistakenly reuse an execution plan for different sessions, causing the temporary table to get shared across sessions.

This is why one user might see data from another user — the execution plan is shared, and SQL Server doesn’t isolate the temp table as expected.

 

Adding <strong>WITH RECOMPILE</strong> forces SQL Server to compile a fresh execution plan every time the procedure is called. This recompile behaviour ensures each user session gets its unique temporary table instance, as SQL Server treats each call as a separate execution with a clean slate.

This approach effectively bypasses any potential cache reuse issue. It's noted that this can lead to an increase in CPU usage due to constant recompilation.

https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/recompile-a-stored-procedure

 

 

Confirming what you have read looks to be true, interested if you do find a different way around this session concern without the need for extra CPU overhead.

hnbright

Thanks Jon_Kirkwood,

This is exactly what happened for me, later along with the googled suggestions i used the WITH RECOMPILE option. Although i currently don't like the solution but for now its the only thing which is giving us the correct results. Thankfully the CPU is not impacted too much.

Surely when i have a better workaround i shall share here.

I highly appreciate the work RedGate has brought. It has made my work life much easier. Thanks once again.