Adaptive Query Processing

SQL Server 2017 has introduced several changes to the way that query plans work in SQL Server. This article is the fourth in a series that will cover these changes. The other articles in the series are:

Automatic Plan Correction
Adaptive Query Processing – Batch Mode Memory Grant Feedback
Adaptive Query Processing – Batch Mode Adaptive Join
Adaptive Query Processing – Interleaved Execution (this article)

Adaptive Query Processing deals with means to improve query performance. Introduced in SQL Server 2017, Adaptive Query Processing currently includes these features:

Batch Mode Memory Grant Feedback
Batch Mode Adaptive Join
Interleaved Execution

Generally speaking, query optimization generates a set of query plans, during which various plan options are considered and the plan with the lowest cost is used. The query then runs the plan generated by the optimization process. Sometimes, the generated plan just isn’t an optimal plan for the query being run as a different number of rows being returned can drastically change the best plan for a query.

Interleaved Execution

In the above description, notice how the process is one way. The query optimization process generates the query plan, and then the query executes it. Unfortunately, there are scenarios where SQL Server doesn’t know and can’t get accurate cardinality estimates. One such example is a multi-statement table valued function (MSTVF). Since the onset of MSTVFs, they have generated a cardinality of 1 row. In SQL Server 2014, this was enhanced to have a cardinality of 100 rows. It doesn’t matter how many rows the MSTVF actually does generate, this hard-coded value is what the query optimizer thinks will be returned. In earlier articles of this series, we’ve already seen how having inaccurate row estimates can lead to poorly performing queries.

How interleaved execution works is that the above process becomes two way – when the query optimization phase sees an operation that is a candidate for interleaved execution (currently, only a MSTVF), the optimization process pauses, that subtree is executed, accurate cardinality estimates are captured, and then the optimization resumes using those newly acquired cardinality estimates.

Seeing Interleaved Execution in Action

For this demo, we will be using the WideWorldImportersDW database, available from Microsoft’s github for SQL Server samples. First, we need to create a MSTVF.

USE [WideWorldImportersDW]
GO
/****** Object:  UserDefinedFunction [Fact].[WhatIfOutlierEventQuantity]    Script Date: 5/4/2017 4:02:36 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER FUNCTION [Fact].[WhatIfOutlierEventQuantity](@event VARCHAR(15), @beginOrderDateKey DATE, @endOrderDateKey DATE)
RETURNS @OutlierEventQuantity TABLE (
       [Order Key] [bigint],
       [City Key] [int] NOT NULL,
       [Customer Key] [int] NOT NULL,
       [Stock Item Key] [int] NOT NULL,
       [Order Date Key] [date] NOT NULL,
       [Picked Date Key] [date] NULL,
       [Salesperson Key] [int] NOT NULL,
       [Picker Key] [int] NULL,
       [OutlierEventQuantity] [int] NOT NULL)
AS
BEGIN
-- Valid @event values
       -- 'Mild Recession'
       -- 'Hurricane - South Atlantic'
       -- 'Hurricane - East South Central'
       -- 'Hurricane - West South Central'
       IF @event = 'Mild Recession'
    INSERT  @OutlierEventQuantity
       SELECT [o].[Order Key], [o].[City Key], [o].[Customer Key],
           [o].[Stock Item Key], [o].[Order Date Key], [o].[Picked Date Key],
           [o].[Salesperson Key], [o].[Picker Key],
           CASE
        WHEN [o].[Quantity] > 2 THEN [o].[Quantity] * .5
        ELSE [o].[Quantity]
    END
       FROM [Fact].[Order] AS [o]
       INNER JOIN [Dimension].[City] AS [c]
 ON [c].[City Key] = [o].[City Key]
       IF @event = 'Hurricane - South Atlantic'
    INSERT  @OutlierEventQuantity
       SELECT [o].[Order Key], [o].[City Key], [o].[Customer Key],
           [o].[Stock Item Key], [o].[Order Date Key], [o].[Picked Date Key],
           [o].[Salesperson Key], [o].[Picker Key],
           CASE
        WHEN [o].[Quantity] > 10 THEN [o].[Quantity] * .5
        ELSE [o].[Quantity]
    END
       FROM [Fact].[Order] AS [o]
       INNER JOIN [Dimension].[City] AS [c]
 ON [c].[City Key] = [o].[City Key]
       WHERE [c].[State Province] IN
       ('Florida', 'Georgia', 'Maryland', 'North Carolina',
       'South Carolina', 'Virginia', 'West Virginia',
       'Delaware')
       AND [o].[Order Date Key] BETWEEN @beginOrderDateKey AND @endOrderDateKey
       IF @event = 'Hurricane - East South Central'
    INSERT  @OutlierEventQuantity
       SELECT [o].[Order Key], [o].[City Key], [o].[Customer Key],
           [o].[Stock Item Key], [o].[Order Date Key], [o].[Picked Date Key],
           [o].[Salesperson Key], [o].[Picker Key],
           CASE
        WHEN [o].[Quantity] > 50 THEN [o].[Quantity] * .5
        ELSE [o].[Quantity]
    END
       FROM [Fact].[Order] AS [o]
       INNER JOIN [Dimension].[City] AS [c]
 ON [c].[City Key] = [o].[City Key]
       INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [si].[Stock Item Key] = [o].[Stock Item Key]
       WHERE [c].[State Province] IN
       ('Alabama', 'Kentucky', 'Mississippi', 'Tennessee')
       AND [si].[Buying Package] = 'Carton'
       AND [o].[Order Date Key] BETWEEN @beginOrderDateKey AND @endOrderDateKey
       IF @event = 'Hurricane - West South Central'
    INSERT  @OutlierEventQuantity
       SELECT [o].[Order Key], [o].[City Key], [o].[Customer Key],
           [o].[Stock Item Key], [o].[Order Date Key], [o].[Picked Date Key],
           [o].[Salesperson Key], [o].[Picker Key],
           CASE
     WHEN [cu].[Customer] = 'Unknown' THEN 0
        WHEN [cu].[Customer] <> 'Unknown' AND
         [o].[Quantity] > 10 THEN [o].[Quantity] * .5
        ELSE [o].[Quantity]
    END
       FROM [Fact].[Order] AS [o]
       INNER JOIN [Dimension].[City] AS [c]
 ON [c].[City Key] = [o].[City Key]
       INNER JOIN [Dimension].[Customer] AS [cu]
       ON [cu].[Customer Key] = [o].[Customer Key]
       WHERE [c].[State Province] IN
       ('Arkansas', 'Louisiana', 'Oklahoma', 'Texas')
       AND [o].[Order Date Key] BETWEEN @beginOrderDateKey AND @endOrderDateKey
    RETURN
END
GO

Let’s now look at how this MSTVF functions in SQL 2016. This will be accomplished by setting the compatibility mode to SQL 2016. Turn on the Actual Execution Plans option, then run this code:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 130; --<<<< SQL 2016 - the way it's always worked
GO
USE [WideWorldImportersDW];
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
-- Include Actual Execution Plan
GO
-- Our "before" state
SELECT  [fo].[Order Key], [fo].[Description], [fo].[Package],
 [fo].[Quantity], [foo].[OutlierEventQuantity]
FROM    [Fact].[Order] AS [fo]
INNER JOIN [Fact].[WhatIfOutlierEventQuantity]('Mild Recession',
  '1-01-2013',
  '10-15-2014') AS [foo] ON [fo].[Order Key] = [foo].[Order Key]
  AND [fo].[City Key] = [foo].[City Key]
  AND [fo].[Customer Key] = [foo].[Customer Key]
  AND [fo].[Stock Item Key] = [foo].[Stock Item Key]
  AND [fo].[Order Date Key] = [foo].[Order Date Key]
  AND [fo].[Picked Date Key] = [foo].[Picked Date Key]
  AND [fo].[Salesperson Key] = [foo].[Salesperson Key]
  AND [fo].[Picker Key] = [foo].[Picker Key]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE   [si].[Lead Time Days] > 0
 AND [fo].[Quantity] > 50;

This generates this query plan:

Let’s look at those warnings. First, the left Hash Join:

Then the right one:

And finally, let’s look at the MSTVF:

We can see that it estimated 100 rows. Now we go to SQL 2017 compatibility mode and clear the procedure cache:

USE [master];
GO
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140; --<<< SQL 2017 - the good stuff is here!
GO
USE [WideWorldImportersDW];
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Ensure that Actual Execution Plans is turned on, then run this query again:

SELECT  [fo].[Order Key], [fo].[Description], [fo].[Package],
 [fo].[Quantity], [foo].[OutlierEventQuantity]
FROM    [Fact].[Order] AS [fo]
INNER JOIN [Fact].[WhatIfOutlierEventQuantity]('Mild Recession',
  '1-01-2013',
  '10-15-2014') AS [foo] ON [fo].[Order Key] = [foo].[Order Key]
  AND [fo].[City Key] = [foo].[City Key]
  AND [fo].[Customer Key] = [foo].[Customer Key]
  AND [fo].[Stock Item Key] = [foo].[Stock Item Key]
  AND [fo].[Order Date Key] = [foo].[Order Date Key]
  AND [fo].[Picked Date Key] = [foo].[Picked Date Key]
  AND [fo].[Salesperson Key] = [foo].[Salesperson Key]
  AND [fo].[Picker Key] = [foo].[Picker Key]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE   [si].[Lead Time Days] > 0
 AND [fo].[Quantity] > 50;

This execution plan is:

Notice that there are not any warnings, and that the query plan has a slightly different shape. Looking at the MSTVF:

Notice that the Estimated Number of Rows… well, it’s not 100 anymore. And with seeing this, we can see why we had those warnings. Without having a decent cardinality estimate, the downstream operations did not have a sufficient memory grant, causing the spills. We can see this in the select operators between the two plans. Note also the difference in the estimated number of rows.

SQL 2016 compatibility mode:

SQL 2017 compatibility mode:

In conclusion, Interleaved Execution allows the Query Optimizer to get accurate statistics information for queries involving Multi-Statement Table Valued Functions, eliminating spills and improving performance.

Adaptive Query Processing – Interleaved Execution