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:

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.

Resolution

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.