Poor performance when you run INSERT.. SELECT operations in SQL Server 2016

Symptoms
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.
Cause
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.
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 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.

References 

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


Vlastnosti

ID článku: 3180087 – Posledná kontrola: 11/17/2016 18:08:00 – Revízia: 3.0

Microsoft SQL Server 2016 Developer, Microsoft SQL Server 2016 Standard, Microsoft SQL Server 2016 Enterprise

  • KB3180087
Pripomienky