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

2017-12-24

12087
0
SQLPerformance
2017-12-26

在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