[Performance Tuning] Parallel INSERT … SELECT with TABLOCK hint

SQL Server 2016 新增加 INSERT … SELECT with TABLOCK hint，支援 Parallel 平行處理來提升效能。

先前版本，雖然 TABLOCK，採取減少 Log 量 與 Lock 方式來提升效能。 但在 SQL Server 2016 再強化為 支援 Parallel 平行處理來優化效能。


基本規則是

• 資料表沒有 Clustered Index，也就是 Heap(堆積)。
• 資料庫的 COMPATIBILITY_LEVEL (相容性) = 130 含以上。
• INSERT ... SELECT 使用 TABLCOK。

Parallel INSERT … SELECT with TABLOCK hint

• Table Without Clustered Index.
• The database compatibility level must be 130 and above.
• Must use the TABLOCK hint with the INSERT … SELECT statement.

使用 TABLOCK，效能提升：71.5% 。

(0.061514000-0.105527000)/0.061514000 = 71.5%

TSQL	LastElapsedTime(sec)
#th1：with TABLOCK	0.061514000
#th2：without TABLOCK	0.105527000
#th3_ClusteredIndex：with TABLOCK, and with Clustered Index	0.123723000

---

[Performance Tuning] Parallel INSERT … SELECT with TABLOCK hint




01. 建立 temporary table (暫存資料表)。

• #th1 : 沒有 Clustered Index，也就是 Heap (堆積)。
• #th2 : 沒有 Clustered Index，也就是 Heap (堆積)。

-- figure 01_Create Table_without_Clustered Index

02. 建立 temporary table (暫存資料表)。

• #th3_ClusteredIndex : 有 Clustered Index。
• TransactionID 資料行，設定為 Clustered Index，也是 Primary Key。

-- figure 02_Create Table With Clustered Index

03. 新增資料列，使用 INSERT ... SELECT

• #th1 : INSERT ... SELECT ，並加上使用 TABLOCK 。
• Parallel Insert，平行處理。
• #th2 : 僅使用 INSERT ... SELECT 。
• 無法 Parallel Inert，無法平行處理。

-- figure 11_WITH (TABLOCK)

04. 觀察 Execution Plan，兩相比較下

• 有使用 TABLOCK hint，僅占 36% 成本資源。
• 觀察到有 Parallelism 平行處理的 圖示。

-- figure 12_Execution_Plan

05. 使用 Plan Explorer 來做觀察：

• 有使用 TABLOCK，Cost 僅使用 36%，Parallel Operations 是 4 。
• 沒用 TABLOCK，Cost 多耗用至 63.8%，Parallel Operations 是 1 。

-- figure 13_Execution_Plan_Plan_Explorer

06. 進一步觀察 Execution Plan:

• 有使用 TABLOCK，Subtree Cost 是: 3.34141。
• 沒用 TABLOCK，Subtree Cost 高達: 5.89468。

-- figure 14

-- figure 15

07. 觀察 Table Insert operator:

• 有使用 TABLOCK，可以 Parallel 平行處理。
• 留意：Number of Executions 是：4。

• 沒有 TABLOCK，不支援 Parallel 平行處理。
• 留意：Number of Executions 是：1。

-- figure 16_Table_Insert

-- figure 17_Table_Insert

08. 觀察 Performance Data：

• 使用 TABLOCK，效能提升：71.5% 。
• Improvement = (Original_Response_Time - Improved_Response_Time) / Original_Response_Time * 100%
• (0.061514000-0.105527000)/0.061514000 = 71.5%

TSQL	LastElapsedTime(sec)
#th1：with TABLOCK	0.061514000
#th2：without TABLOCK	0.105527000
#th3_ClusteredIndex：with TABLOCK, and with Clustered Index	0.123723000

-- figure 18_Performance_Data

09. 比較以下 INSERT ... SELECT ：

• #th1：with TABLOCK
• 可 平行處理。Parallel Insert
• #th2：without TABLOCK
• 無法 平行處理。Not Parallel
• #th3_ClusteredIndex：with TABLOCK, and with Clustered Index
• 無法 平行處理。Not Parallel

-- figure 19_Different type of INSERT ... SELECT

10. 觀察 Execution Plan

-- figure 20_Compare_3

---

The database compatibility level must be 130 and above.


01. 調降 database compatibility level 為 120。


-- figure 21_database compatibility level

02. 確認該資料庫是 降級使用 COMPATIBILITY_LEVEL = 120

-- figure 22_COMPATIBILITY_LEVEL = 120

03. 再度執行 INSERT ... SELECT

-- figure 23_INSERT_SELECT

04. 觀察 Execution Plan:

• 都不支援 平行處理。
• Non-Parallel execution in all script.

這是由於 COMPATIBILITY_LEVEL 必須要在 130 以上才支援 平行處理。

-- figure 24_Execution_Plan

---

大量匯入資料的最佳做法

使用 INSERT INTO…SELECT 搭配最低限度記錄來大量匯入資料
您可以搭配使用 INSERT INTO SELECT FROM 最低限度記錄，

有效率地將大量資料列從某份資料表 (例如暫存表格) 傳送至另一份資料表。

最低限度記錄可以改善此陳述式的效能並且降低交易期間作業填滿可用交易記錄空間的可能性。

此陳述式的最低限度記錄具有下列需求：

1. 資料庫的復原模式設為 simple (簡單) 或 bulk-logged(大量記錄)。
2. 目標資料表是空白或非空白的 heap(堆積)。
3. 目標資料表未用於 複寫。
4. 針對目標資料表指定了 TABLOCK 提示。

由於 MERGE 陳述式中的插入動作而插入堆積的資料列也可以採用最低限度記錄。

與 BULK INSERT 陳述式 (持有較不嚴格的大量更新鎖定) 不同之處在於，
具 TABLOCK 提示的 INSERT INTO…SELECT 對資料表持有獨佔 (X) 鎖定。
這代表您無法使用平行插入作業插入資料列。

---

Sample Code

20180724_INSERT_SELECT_TABLOCK
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

---

Reference

SQLSweet16!, Episode 3: Parallel INSERT … SELECT
https://blogs.msdn.microsoft.com/sqlcat/2016/07/06/sqlsweet16-episode-3-parallel-insert-select/

Real World Parallel INSERT…SELECT: What else you need to know!
https://blogs.msdn.microsoft.com/sqlcat/2016/07/21/real-world-parallel-insert-what-else-you-need-to-know/

Columnstore Index: Parallel load into clustered columnstore index from staging table
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/07/19/columnstore-index-parallel-load-into-clustered-columnstore-index-from-staging-table/

The Data Loading Performance Guide
https://docs.microsoft.com/zh-tw/previous-versions/sql/sql-server-2008/dd425070(v=sql.100)

Poor performance when you run INSERT.. SELECT operations in SQL Server 2016 or SQL Server 2017 on Windows
https://support.microsoft.com/en-us/help/3180087/poor-performance-when-you-run-insert-select-operations-in-sql-server-2

INSERT (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-2017