DB-유

Intelligent Query Processing in SQL Server 2019

· mssql, sql server 2019, query optimizer, performance

I've got a handful of line-of-business databases full of scalar functions and table variables, and they have hurt for years. So I was pleased to find that Intelligent Query Processing in SQL Server 2019 needs no code changes: set the compatibility level to 150 and the optimizer improvements apply to existing queries.

Here's what's in the family, how to see each feature working, and, more important, how to switch one off when it misbehaves. I test on a copy of production at level 150 with Query Store on, so I can compare before and after. Any edition works; a few features are Enterprise-only, noted below.

Raise the compatibility level

Everything in IQP is gated on the compatibility level, so it can be tested per database:

ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 150;
SELECT name, compatibility_level FROM sys.databases WHERE name = 'Sales';

A restored database keeps its old level, so check rather than assume. On a production system I keep Query Store running under the old level for a couple of weeks first so there is a baseline, then flip the level and watch the Regressed Queries report.

Scalar UDF inlining

Scalar user-defined functions called in a SELECT list or WHERE clause have always executed row by row, serialised the plan, and hidden their cost from the optimizer. Under level 150, functions that qualify are inlined into the calling query as relational expressions, so the query runs as one set-based plan and can go parallel.

See which functions qualify:

SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) AS function_name, is_inlineable
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1;

Common reasons for is_inlineable = 0: the function uses GETDATE() or other time-dependent intrinsics, has WITH SCHEMABINDING referencing another UDF, uses WHILE loops or TRY...CATCH, or is called with ORDER BY on the result. Rewriting a small function to avoid those often makes it inlineable.

To disable inlining for one function without lowering the level:

ALTER FUNCTION dbo.fn_CustomerDiscount (@CustomerID int) RETURNS decimal(5,2)
WITH INLINE = OFF
AS BEGIN ... END;

Table variable deferred compilation

Table variables used to be estimated at exactly one row when the batch compiled, which produced nested loops joins over a hundred thousand rows. Under level 150 the statement referencing the table variable is compiled when it first executes, using the actual row count. No syntax change; you can see it in the plan where the estimated rows on the table variable scan now match the actual.

If a specific procedure was tuned around the old behaviour and regresses:

SELECT ... FROM @Orders o JOIN dbo.Customers c ON c.CustomerID = o.CustomerID
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));

Batch mode on rowstore and memory grant feedback

Batch mode execution was tied to columnstore indexes since 2012. In 2019 the optimizer can use batch mode operators on ordinary rowstore tables for analytic-style queries (large scans, aggregates, joins), which can cut CPU time for reports substantially. It is Enterprise-only. Look for Actual Execution Mode = Batch on operators in the actual plan.

Row mode memory grant feedback extends the 2017 batch mode feature: when a query asks for far too much or too little memory (spills to tempdb or wastes a grant), the next execution of the same plan gets an adjusted grant. The plan XML shows IsMemoryGrantFeedbackAdjusted with values such as YesAdjusting and YesStable. It does nothing for queries whose grant is already right, and it gives up (NoFeedbackDisabled) if the requirements keep oscillating.

Approximate count distinct and the rest

APPROX_COUNT_DISTINCT is the one feature that needs a code change. It returns a HyperLogLog estimate with about 2 percent error, using a tiny fraction of the memory of COUNT(DISTINCT ...) on very large tables:

SELECT APPROX_COUNT_DISTINCT(CustomerID) FROM dbo.WebEvents;

Adaptive joins and interleaved execution for multi-statement table-valued functions came in 2017 at level 140 and remain part of the family; both are Enterprise-only for adaptive joins.

Switching single features off

You don't have to give up level 150 because one feature hurts. I keep the level and turn off only the offender. There's a database scoped configuration for each:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;
ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = OFF;

Check what is set:

SELECT name, value FROM sys.database_scoped_configurations
WHERE name IN ('TSQL_SCALAR_UDF_INLINING', 'DEFERRED_COMPILATION_TV', 'BATCH_MODE_ON_ROWSTORE', 'ROW_MODE_MEMORY_GRANT_FEEDBACK');

Measuring the difference

Pick a query that calls a scalar UDF per row and compare before and after in Query Store:

SELECT p.plan_id, p.query_id, rs.avg_duration / 1000 AS avg_ms, rs.avg_cpu_time / 1000 AS avg_cpu_ms, rs.count_executions
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
WHERE p.query_id = 812 ORDER BY rs.last_execution_time DESC;

With inlining active, the plan XML no longer contains a UserDefinedFunction node and the ContainsInlineScalarTsqlUdfs="true" attribute appears on the statement.

When it goes wrong

  • A query slows down after the level change and the plan shows a huge estimate on a table variable. Deferred compilation is now using the real row count, and the old plan was accidentally good. Add the DISABLE_DEFERRED_COMPILATION_TV hint for that statement or rewrite with a temp table.
  • Several inlining bugs in the RTM build, including wrong results with certain CASE and ISNULL patterns inside functions and access violations during compilation, were fixed in CU1 (released early this month). Apply CU1 or later before relying on inlining in production.
  • A function returns different results after inlining. Inlining changes evaluation order for functions with side effects or non-deterministic behaviour; those should not have been inlineable and CU1 tightened the checks. Set WITH INLINE = OFF on that function.
  • Batch mode on rowstore does not kick in. It needs a table with at least 131,072 rows and a query the optimizer considers analytical; small tables and simple seeks stay in row mode by design.

Still, test with Query Store running. Rare upgrade where things mostly get faster without touching the app.