SQL Server 2019 introduces several performance optimizations which will improve performance with minimal changes required to your application code. In this blog post we’ll discuss one such improvement available in CTP 2.3: reduced recompilations for workloads using temporary tables in multiple scopes.
In order to understand this improvement, we’ll first go over the current behavior prior to SQL Server 2019.
When referencing a temporary table with a DML statement (SELECT, INSERT, UPDATE, DELETE), if the temporary table was created by an outer scope batch, we will recompile the DML statement each time it is executed.
The following example illustrates this behavior:
In the outer procedure we:
The inner stored procedure definition is as follows:
For the inner stored procedure, we have two DML statements that reference the temporary table created in the outer scope where we:
We created the temporary table in a different scope from the DML statements, and for the existing implementation (pre-SQL Server 2019), we don’t “trust” that this temporary table schema hasn’t been materially changed and so we recompile the associated DML statements each time they are executed.
This additional recompilation activity increases CPU utilization and can decrease overall workload performance and throughput.
Starting with SQL Server 2019 CTP 2.3, we will perform additional lightweight checks to avoid unnecessary recompilations:
The end result is a reduction in unwarranted recompilations and associated CPU-overhead.
The below figure shows test results from 16 concurrent threads each executing the “OutProc” stored procedure 1,000 times (in a loop). The Y-axis represents the number of occurrences, with the blue line representing Batch Requests/sec and the green line representing SQL Re-Compilations/sec:
When the feature was enabled (“after”), for this example we saw:
This feature is enabled by default in SQL Server 2019 CTP 2.3 under all database compatibility levels. As of this writing, this feature is also enabled in Azure SQL Database under database compatibility level 150, although it will soon be applied across all database compatibility levels (matching the behavior of SQL Server 2019).
If you have feedback on this feature or other query processing features, please email us at IntelligentQP@microsoft.com.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.