SQL Server 2019: Intelligent Query Processing in Action

SQL Server 2019 introduces “Intelligent Query Processing” (IQP), a suite of features that fix performance issues automatically without code changes. We look at Adaptive Joins and Scalar UDF Inlining.

Adaptive Joins

Traditionally, the optimizer chooses Nested Loop vs Hash Join based on *estimated* row count. If estimates are wrong, performance tanks. Adaptive Joins defer this decision until runtime.

flowchart TB
    Start[Query Start] --> Scan[Scan Input]
    Scan --> Check{Row Count > Threshold?}
    Check -->|Yes| Hash[Hash Join]
    Check -->|No| Loop[Nested Loop Join]
    Hash --> Result
    Loop --> Result
    
    style Check fill:#FFF9C4

Scalar UDF Inlining

Scalar User Defined Functions have historically been performance killers because they prevent parallelism and are executed row-by-row. 2019 automatically inlines them into the main query.

-- This UDF, which used to be slow...
CREATE FUNCTION dbo.GetTax(@amt money) RETURNS money AS 
BEGIN
  RETURN @amt * 0.2;
END

-- ...is now compiled as if you wrote:
SELECT OrderId, (Amount * 0.2) as Tax FROM Orders

Key Takeaways

• Upgrade compilation compatibility level to 150 to enable IQP.
• Greatly improves workloads with varying data distribution.
• Reduces the need for manual hint tuning.