[SQL Server][安裝]執行磁碟區維護工作(SQL Server 2016)

2017-11-05

3531
0
SQLInstall

SQL Server 2016開始，SQL安裝精靈到了"伺服器組態"步驟時，除了SQL服務帳戶的設定，還多了一個"執行磁碟區維護工作"(Perform Volume Manintenance Task)的選項，他是一個有關資料庫檔案立即初始化的優化，建立大型資料庫、資料庫還原、或是針對既有資料庫增加大型檔案或改變檔案大小(包含Autogrow)時，跳過補零的初始化作業，可以帶來IO效能上的提升。

為了觀察資料庫磁碟檔案背後的存取紀錄，我們啟用Trace flag 3004及3605，透過SQL error log觀察。

DBCC TRACEON(3004,3605,-1)
go
exec sp_cycle_errorlog
go
exec sp_readerrorlog
go

建立測試用資料庫

這邊先將資料庫初始大小設定很小(5,120KB)，但FileGrowth設定為2GB。

CREATE DATABASE [VolumeManintenanceDb]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'VolumeManintenanceDb', FILENAME = N'C:\SQL\VolumeManintenanceDb.mdf' , SIZE = 5120KB , FILEGROWTH = 2GB )
 LOG ON 
( NAME = N'VolumeManintenanceDb_log', FILENAME = N'C:\SQL\VolumeManintenanceDb_log.ldf' , SIZE = 2048KB , FILEGROWTH = 1GB)
GO

觀察建立資料庫時的紀錄

exec sp_readerrorlog
go

傳說中的初始補零(Zeroing)，系統會用0填滿所有的Page。

不過，因為我們初始挖的很小(5120KB)，建立資料庫時間很快，看不出差異，但我們現在開始新增資料讓資料庫檔案長大。

新增4MB資料(對照組)

建立一個單筆資料4K左右的資料表，然後寫入2的10次交易量(1,024筆)

USE [VolumeManintenanceDb]
CREATE TABLE  T1
(C1 INT IDENTITY,
 C2 CHAR(4000))

INSERT INTO T1
	VALUES ('PerformVolumeManintenance')
DECLARE @I  INT = 0
WHILE @I < 10 
BEGIN
INSERT INTO T1
		SELECT
			C2
		FROM T1
		SET @I = @I + 1
END

新增1,024筆資料花了4秒。