在SQL Server 2014以前,Select Into一直都只能用一條執行緒執行資料表插入,即使Into到tempdb也是;不過到了SQL 2014之後,只要資料庫相容性層級設置為120(SQL 2014)就可以在成本大的語法平行執行Select Into了。來筆記實驗過程。
測試機器的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
啟用平行!
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) 相容性層級