- When you run multiple concurrent INSERT…SELECT operations into temporary tables.
- When you query sys.dm_os_waiting_tasks. In this situation, the resource_description column shows waits on Page Free Space (PFS) pages for multiple requests.
Although INSERT parallelism typically improves query performance, if there's a significant number of concurrent temporary table INSERT…SELECT operations, contention may be significant against PFS pages. In some cases, this may cause an overall decrease in performance.
If you have enabled Trace flag 9495 mentioned in the workaround section, it is recommended to disable and remove the trace flag after applying SQL Server 2016 SP1 as Trace flag 9495 disables parallel insert irrespective of the TABLOCK hint.
Cumulative update informationThis issue was fixed in the following cumulative update for SQL Server 2016:
Latest cumulative update for SQL Server 2016
- Enable trace flag 9495 to disable parallelism during insertion for INSERT…SELECT operations. This trace flag disables parallelism only for the INSERT portion of the INSERT…SELECT operation, and it applies to both temporary and user tables. To enable this trace flag, either use it as a startup parameter (-T9495) or use DBCC TRACEON(9495, -1) from a new session. We recommend that you use the startup parameter for production deployments to avoid having to run the DBCC TRACEON flag each time the server starts.
If you have installed SQL Server 2016 Cumulative Update 1 (CU1), you can also use this trace flag in the following ways:
- At the session level, using DBCC TRACEON(9495)
- At a per-statement level, using OPTION QUERYTRACEON(9495)
- Create an index on the temporary table. The issue that's described in the "Symptoms" section occurs only with temporary table heaps.
- Use the MAXDOP 1 query hint for the problematic INSERT…SELECT operations.
Article ID: 3180087 - Last Review: Nov 17, 2016 - Revision: 1