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:
Make sure that the database uses compatibility level 130. This is a prerequisite for parallel plans with memory-optimized tables.
Install a service update that includes this fix. See the "More information" section.
Enable this update in one of two ways:
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.
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.
This issue is fixed in the following cumulative updates for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
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
Learn about the terminology Microsoft uses to describe software updates.