Reduced recompilations for workloads using temporary tables across multiple scopes

  • Article

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 in SQL Server 2017 and prior. 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:

  1. Create a temporary table
  2. Call a stored procedure

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:

  1. Insert a row into the temporary table.
  2. Return the row from the temporary table.

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:

  • We will check if the outer-scope module used for creating the temporary table at compile time is the same one used for consecutive executions.
  • We will keep track of any data definition language (DDL) changes made at initial compilation and compare them with DDL operations for consecutive executions.

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, for this example we saw:

  • Improved throughput, as represented by Batch Requests/sec (blue line, second hump).
  • Shorter overall workload duration.
  • Reduced recompilations, as represented by SQL Re-Compilations/sec (green line) showing only a small increase at the beginning of the second test.

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.