In Microsoft SQL Server 2016, you may experience decreased performance under the following conditions:
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.
INSERT parallelism for INSERT…SELECT operations was introduced in SQL Server 2016. INSERTs into local temporary tables (only those identified by the # prefix, and not global temporary tables identified by ## prefixes) are now automatically enabled for parallelism without having to designate the TABLOCK hint that non-temporary tables require.
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.
To work around this issue, use one or more of the following methods:
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.
For more information about how to configure trace flags, see the "Remarks" section in the following SQL Server Books Online topic: