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

Á við um: SQL Server 2016 Enterprise CoreSQL Server 2016 EnterpriseSQL Server 2016 Developer


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.


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 usedALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130GO DROP PROCEDURE IF EXISTS dbo.[InsertSalesOrder_Native_Batch]DROP TABLE IF EXISTS dbo.SalesOrder_MemOptGOCREATE 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=100WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNERASBEGIN ATOMICWITH (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;        ENDEND;GO-- Insert sample dataEXECUTE dbo.[InsertSalesOrder_Native_Batch] 1, 100, 500000;GO 10  SET SHOWPLAN_XML ONGO -- 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_MemOptGOSET SHOWPLAN_XML OFFGO-- enable parallel plan for DML with references to memory-optimized tablesDBCC TRACEON (9939)GO SET SHOWPLAN_XML ONGO-- Verify the query plan is parallel.SELECT * INTO #temp FROM SalesOrder_MemOptGO SET SHOWPLAN_XML OFFGO DBCC TRACEOFF (9939)GO



Learn about the terminology Microsoft uses to describe software updates.