How can we help you today? How can we help you today?
Jon_Kirkwood

Activity overview

Latest activity by Jon_Kirkwood

Hi adam_hafner, Thank you for reaching out on the Redgate forum regarding this SQL Test feature request. Shall submit it to the development team for their appraisal of this request.  Will update this post with any updates as they are made available. / comments
Hi adam_hafner,Thank you for reaching out on the Redgate forum regarding this SQL Test feature request.Shall submit it to the development team for their appraisal of this request. Will update this ...
0 votes
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. / comments
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 researc...
0 votes