Poor performance when you run INSERT.. SELECT operations in SQL Server 2016 or SQL Server 2017 on Windows

Applies to: SQL Server 2016 DeveloperSQL Server 2016 StandardSQL Server 2016 Enterprise More

Symptoms


In Microsoft SQL Server 2016 or SQL Server 2017 on Windows, you may experience decreased performance under the following conditions: 
  • When you run multiple concurrent INSERTSELECT 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.

Cause


INSERT parallelism for INSERTSELECT operations was introduced in SQL Server 2016 or SQL Server 2017 on Windows. 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 INSERTSELECT operations, contention may be significant against PFS pages. In some cases, this may cause an overall decrease in performance.

Resolution


The issue is first fixed in SQL Server 2016 Service Pack 1 . After you apply SQL Server 2016 SP1, Parallel INSERTs in INSERT..SELECT to local temporary tables is disabled by default which reduces contention on PFS page and improves the overall performance for concurrent workload. If parallel INSERTs to local temporary tables is desired, users should use TABLOCK hint while inserting into local temporary table.

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 information

This issue was fixed in the following cumulative update for SQL Server 2016:About 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:

Latest cumulative update for SQL Server 2016



Workaround


To work around this issue, use one or more of the following methods:
  • Enable trace flag 9495 to disable parallelism during insertion for INSERTSELECT operations. This trace flag disables parallelism only for the INSERT portion of the INSERTSELECT 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 INSERTSELECT operations.

References 

For more information about how to configure trace flags, see the "Remarks" section in the following SQL Server Books Online topic: