KB4013877 - Update enables DML query plan to scan query memory-optimized tables in parallel in SQL Server 2016

Summary

Before this update, parallel query plans are not supported for DML operations that reference memory-optimized tables or table variables, even if they are not the target of the DML operation. INSERT, UPDATE, DELETE, and MERGE statements would always have a serial plan if there is any reference to memory-optimized tables or table variables, even for cases in which parallel plans are supported with traditional disk-based tables and table variables.

This update adds support for parallel plans and parallel scan of memory-optimized tables and table variables in DML operations that reference memory-optimized tables or table variables, as long as they are not the target of the DML operation. INSERT, UPDATE, DELETE, and MERGE statements that involve modification of a memory-optimized table or table variable continue to be serial.

The following are the three requirements to enable parallel plans for DML statements that reference memory-optimized tables or table variables:

1. Make sure that the database uses compatibility level 130. This is a prerequisite for parallel plans with memory-optimized tables.

2. Install a service update that includes this fix. See the "More information" section.

3. Enable this update in one of two ways:

   1. Turn on trace flag 9939 to enable only this update. This option is recommended for all customers who use memory-optimized tables or table variables and who are not already using option 3b, below.

   2. Enable SQL Server query optimizer hotfixes. You can do this through trace flag 4199, a database-scoped configuration, or a query hint. For more information, see SQLServer query optimizer hotfix trace flag 4199 servicing model.

Resolution

This issue is fixed in the following cumulative updates for SQL Server:

Cumulative Update 5 for SQL Server 2016

Cumulative Update 3 for SQL Server 2016 SP1

Appendix: Example of the fix

The following Transact-SQL script illustrates a parallel plan for a DML operation on a disk-based table that references a memory-optimized table. With this update applied, the script will return two query plans for the same operation. The first is without trace flag 9939 and will be serial. The second is with trace flag 9939 enabled and will be parallel.

-- make sure that you connect to a database that has a memory-optimized filegroup with at least one container
-- the following script creates such a filegroup and container if it does not exist:
--   https://raw.githubusercontent.com/Microsoft/sql-server-samples/master/samples/features/in-memory/t-sql-scripts/enable-in-memory-oltp.sql
 
-- make sure DB compat level 130 is used
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130
GO
 
DROP PROCEDURE IF EXISTS dbo.[InsertSalesOrder_Native_Batch]
DROP TABLE IF EXISTS dbo.SalesOrder_MemOpt
GO
CREATE TABLE dbo.SalesOrder_MemOpt (
    order_id     INT      IDENTITY NOT NULL,
    order_date   DATETIME NOT NULL,
    order_status TINYINT  NOT NULL,
    amount       FLOAT    NOT NULL,
    CONSTRAINT PK_SalesOrderID PRIMARY KEY NONCLUSTERED HASH (order_id) WITH (BUCKET_COUNT = 10000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO
 
-- Natively compiled proc
-- Create Natively compiled procedure to speed up inserts.
CREATE PROCEDURE [dbo].[InsertSalesOrder_Native_Batch]
@order_status TINYINT=1, @amount FLOAT=100, @OrderNum INT=100
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
    DECLARE @counter AS INT = 1;
    WHILE @counter <= @OrderNum
        BEGIN
            INSERT  INTO dbo.SalesOrder_MemOpt
            VALUES (getdate(), @order_status, @amount);
            SET @counter = @counter + 1;
        END
END;
GO
-- Insert sample data
EXECUTE dbo.[InsertSalesOrder_Native_Batch] 1, 100, 500000;
GO 10
 
 
SET SHOWPLAN_XML ON
GO
 
-- Insert into a temp table from memory-optimized table
-- Without the trace flag, this is serial. Inspect the query plan to verify it is serial.
SELECT * INTO #temp FROM SalesOrder_MemOpt
GO
SET SHOWPLAN_XML OFF
GO
-- enable parallel plan for DML with references to memory-optimized tables
DBCC TRACEON (9939)
GO
 
SET SHOWPLAN_XML ON
GO
-- Verify the query plan is parallel.
SELECT * INTO #temp FROM SalesOrder_MemOpt
GO
 
SET SHOWPLAN_XML OFF
GO
 
DBCC TRACEOFF (9939)
GO

References

Learn about the terminology Microsoft uses to describe software updates.