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

在SQL Server 2014以前,Select Into一直都只能用一條執行緒執行資料表插入,即使Into到tempdb也是;不過到了SQL 2014之後,只要資料庫相容性層級設置為120(SQL 2014)就可以在成本大的語法平行執行Select Into了。來筆記實驗過程。Parallel process icon

測試機器的CPU有16核心,所使用的硬碟是SSD,tempdb和user db放在同一顆硬碟上。實驗時會有4個組別,分別使用新舊的相容性層級在user db和tempdb執行寫入。

  • SQL 2012相容性層級 + Select Into User DB
  • SQL 2012相容性層級 + Select Into TempDB
  • SQL 2014相容性層級 + Select Into User DB
  • SQL 2014相容性層級 + Select Into TempDB

建立環境

新增一個SelectIntoDb

CREATE DATABASE [SelectIntoDb]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SelectIntoDb1', FILENAME = N'F:\SQL\SelectIntoDb1.mdf' , SIZE = 4GB , FILEGROWTH = 1GB),
( NAME = N'SelectIntoDb2', FILENAME = N'F:\SQL\SelectIntoDb2.ndf' , SIZE = 4GB , FILEGROWTH = 1GB),
( NAME = N'SelectIntoDb3', FILENAME = N'F:\SQL\SelectIntoDb3.ndf' , SIZE = 4GB , FILEGROWTH = 1GB),
( NAME = N'SelectIntoDb4', FILENAME = N'F:\SQL\SelectIntoDb4.ndf' , SIZE = 4GB , FILEGROWTH = 1GB)

 LOG ON 
( NAME = N'SelectIntoDb_log', FILENAME = N'L:\SQL\SelectIntoDb.ldf' , SIZE = 4GB , FILEGROWTH = 1GB)
GO

新增500萬筆資料

USE [SelectIntoDb]
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

USE SelectIntoDb
EXEC sp_spaceused 'T1'

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

 


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

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

ALTER DATABASE [SelectIntoDb] SET COMPATIBILITY_LEVEL = 110;
GO

執行Select Into測試

USE [SelectIntoDb]
SET STATISTICS IO ON 
SET STATISTICS TIME ON 
SELECT * INTO T1_1 FROM T1

 

CPU時間2828 ms及經過時間2952 ms

執行計畫中資料表插入沒有平行符號,另一個有趣的是連T1的叢集掃描也沒平行。

未啟用平行

 


2.對照組測試(未啟用平行,寫到tempdb)

執行測試

USE [SelectIntoDb]
SET STATISTICS IO ON 
SET STATISTICS TIME ON 
SELECT * INTO #T1 FROM T1

 

CPU TIME 2531 ms,經過時間 2643 ms

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

未啟用平行

 


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

設定相容性層級到120(SQL Server 2014)

ALTER DATABASE [SelectIntoDb] SET COMPATIBILITY_LEVEL = 120;
GO

重新執行Select Into測試

USE [SelectIntoDb]
SET STATISTICS IO ON 
SET STATISTICS TIME ON 
SELECT * INTO T1_2 FROM T1 

 

CPU TIME 5297 ms,經過時間1454 ms

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

啟用平行!

 


4.實驗組測試(啟用平行,寫到tempdb)

執行測試

USE [SelectIntoDb]
SET STATISTICS IO ON 
SET STATISTICS TIME ON 
SELECT * INTO #T1 FROM T1

 

CPU TIME 5123 ms,經過時間827 ms

執行計畫

啟用平行

 

最後把測試資料庫刪除~

ALTER DATABASE [SelectIntoDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
DROP DATABASE [SelectIntoDb] 

 


小結

相容性層級 CPU TIME 經過時間 估計子樹成本
110 2828 ms 2952 ms 594
110(tempdb) 2531 ms 2643 ms 594
120 5297 ms 1454 ms 195
120(tempdb) 5123 ms 827 ms 195
  • 選擇120的相容性層級來啟用SELECT INTO平行後,執行速度快了,不過CPU TIME也增加了。
  • temp table也許用memory optimized table更佳(建議SQL 2016)
  • 相容性層級升級後,也有副作用,尤其是基數預估演算法(Cardinality Estimator)的改變,要測試先。
  • 可能資料量不夠,比較不出平行倍數的優勢,這一篇blog的差異很驚人。

 

台北田徑暖身場,吃課表,會變快,順便減壓。

 

 


參考:

Graphical Execution Plan Icons (SQL Server Management Studio)

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