[SQL Server][Performance]INSERT INTO SELECT啟用平行寫入(SQL Server 2016)

在SQL 2016以前,Insert Into Select一直都只能用一條執行緒執行資料表插入,SQL Server先在2014版本時優化了Select Into新增了平行,到了SQL 2016版本也優化了Insert Into Select,只要資料庫相容性層級設置為130(SQL 2016),搭配WITH (TABLOCK)的Table Hint,我們也可以在非叢集索引資料表平行執行Insert Into Select了。來筆記實驗過程。

Parallel process icon

測試機器的CPU有16核心,分別使用新舊的相容性層級在user db執行寫入。


建立環境

新增一個InsertIntoSelectDb

CREATE DATABASE [InsertIntoSelectDb]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'InsertIntoSelectDb1', FILENAME = N'F:\Data\InsertIntoSelectDb1.mdf' , SIZE = 4GB , FILEGROWTH = 1GB),
( NAME = N'InsertIntoSelectDb2', FILENAME = N'F:\Data\InsertIntoSelectDb2.ndf' , SIZE = 4GB , FILEGROWTH = 1GB),
( NAME = N'InsertIntoSelectDb3', FILENAME = N'F:\Data\InsertIntoSelectDb3.ndf' , SIZE = 4GB , FILEGROWTH = 1GB),
( NAME = N'InsertIntoSelectDb4', FILENAME = N'F:\Data\InsertIntoSelectDb4.ndf' , SIZE = 4GB , FILEGROWTH = 1GB)

 LOG ON 
( NAME = N'InsertIntoSelectDb_log', FILENAME = N'F:\Data\InsertIntoSelectDb.ldf' , SIZE = 4GB , FILEGROWTH = 1GB)
GO

新增100萬筆資料

USE [InsertIntoSelectDb]
CREATE TABLE T1
(ID INT IDENTITY,C2 CHAR(100),C3 uniqueidentifier, PRIMARY KEY(ID))

SET NOCOUNT ON 
DECLARE @I INT = 0
BEGIN TRAN
WHILE @I < 5000000
BEGIN
INSERT INTO T1
	VALUES (CONVERT(VARCHAR,@I),NEWID())
SET @I = @I + 1
END
COMMIT

查看測試資料表T1

EXEC sp_spaceused 'T1'

我們有一個600MB的測試資料表了

 


1.對照組測試(未啟用平行)

設定相容性層級退到110(SQL Server 2012)

ALTER DATABASE [InsertIntoSelectDb] SET COMPATIBILITY_LEVEL = 110;
GO

執行Insert Into Select 測試

USE InsertIntoSelectDb
SET STATISTICS IO ON 
SET STATISTICS TIME ON 
CREATE TABLE T1_1
(
ID INT ,
C2 CHAR(100),
C3 uniqueidentifier, 
)
INSERT INTO  T1_1 WITH (TABLOCK) SELECT * FROM T1

 

CPU TIME 2125ms,經過時間2386ms

執行計畫中資料表插入沒有平行符號

未啟用平行

 


2.實驗組測試(啟用平行)

設定相容性層級到130(SQL Server 2016)

ALTER DATABASE [InsertIntoSelectDb] SET COMPATIBILITY_LEVEL = 130;
GO

執行Insert Into測試,要加上WITH (TABLOCK) 才會啟用平行

USE InsertIntoSelectDb
SET STATISTICS IO ON 
SET STATISTICS TIME ON 
CREATE TABLE T1_2
(
ID INT ,
C2 CHAR(100),
C3 uniqueidentifier, 
)
INSERT INTO  T1_2 WITH (TABLOCK) SELECT * FROM T1

 

CPU TIME 4560ms,經過時間1315ms

執行計畫: 資料表插入走平行了,連T1叢集掃描也平行了,平行處理原則是8
Parallel process icon

啟用平行

 


小結

相容性層級 CPU TIME 經過時間 估計子樹成本
110 2125ms 2386ms 593
130 4560ms 1315ms 194
  • 啟用平行後,執行時間快了一些,CPU 時間也增加了。ms blog的在速度上差距到3x
  • 除了相容性層級130(SQL2016)以上外,還要加上Table Hint WITH (TABLOCK)
  • 相容性層級升級後,也有副作用,尤其是基數預估演算法(Cardinality Estimator)的改變,要測試先。

 


參考:

Graphical Execution Plan Icons (SQL Server Management Studio)

ALTER DATABASE (TRANSACT-SQL) 相容性層級

SQLSweet16!, Episode 3: Parallel INSERT … SELECT

Docs 提示 (TRANSACT-SQL)-資料表