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

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.