使用檔案立即初始化提高SQL Server效能

標籤：

今天我想談下SQL Server裡的一個特別話題——檔案立即初始化（Instant File Initialization）。對於你的SQL Server執行個體，如果你啟用了檔案立即初始化，在特定情況下，你會獲得巨大的效能提升。檔案立即初始化定義了當在資料檔案裡分配新的空間時，SQL Server引擎如何和Windows作業系統打交道。

問題緣由

在SQL Server預設配置裡，當你在資料檔案裡分配新空間時，SQL Server會調用內部WIN32 API函數，填0初始化新分配的NTFS簇。這就是說新分配的空間的每個位元組會用0值（0x0）重寫。這個舉動會阻止訪問到未經處理資料問題，即在同個NTF簇裡實體儲存體的資料。在下列SQL Server操作中，會發生填0初始化：

• 建立新的資料庫
• 資料庫自動成長
• Database Backup還原

當你建立10GB的資料庫檔案，SQL Server第1步需要把10GB的資料區塊寫上0值。這個會花費很長時間。我們來看下面CREATE DATABASE語句：

 1 -- Create a new 10 GB database 2 CREATE DATABASE TestDatabase ON PRIMARY 3 (  4     NAME = N‘TestDatabase‘, 5     FILENAME = N‘D:\SQL\DATA\TestDatabase.mdf‘ ,  6     SIZE = 10240000KB ,  7     FILEGROWTH = 1024KB 8 ) 9 LOG ON 10 (11     NAME = N‘TestDatabase_log‘, 12     FILENAME = N‘D:\SQL\Log\TestDatabase_log.ldf‘ ,13     SIZE = 1024KB , 14     FILEGROWTH = 10%15 )16 GO

從代碼可以看到，這裡我建立10GB的資料庫檔案。在我SQL Server預設配置裡，這個語句花費了近49秒，因為SQL Server通過WIN32 API函數寫入10GB的0到儲存。假設你有損壞的資料庫（例如也是10GB），你想還原備份，會發生什嗎？在第1步通常人們會怎麼做？是會刪除損壞的資料庫。這意味這你的資料庫檔案沒了，在還原檔案操作期間，SQL Server第1步需要重建檔案。

1. SQL Server第1步建立10GB“空”的資料庫，在NTF檔案系統裡資料檔案會被填0初始化。
2. 最後1步備份被還原時，SQL Server再次寫入10GB的資料到資料檔案。

你會發現，你寫了近20GB的資料到你的儲存！如果你在現存的檔案上還原你的備份，SQL Server會跳過第1步，直接寫入10GB的資料到你的儲存——你就獲得了100%的效能提升！

檔案立即初始化

如果你不想SQL Server進行你資料檔案的填0初始化，你可以重新設定SQL Server。如果你授權SERVICE帳號，在對應啟動並執行SQL Server下——執行卷維護任務（Performance Volume Maintenance Task）特權，在你重啟SQL Server後，SQL Server會跳過資料檔案的填0初始化。我曾說過這隻對資料檔案有效——在SQL Server裡記錄檔還是總要填0初始化的！這是米有辦法滴！！！如果記錄檔沒有填0初始化，當記錄檔被包裹時，故障還原進程就不知道從哪裡結束。故障還原停在它找到下條日誌記錄需要處理頭部0值地方。

你可以通過secpol.msc對SQL Server的SERVICE帳號授予執行卷維護任務（Performance Volume Maintenance Task）特權。

在重啟後，SQL Server現在可以跳過資料檔案的填0初始化。當我再次執行CREATE DATABASE，它只花費了近210ms——那是巨大的區別！副作用呢？你可以通過DBCC PAGE命令獲得在分配的NTFS簇裡儲存的原始內容： 

1 -- Enable DBCC trace flag 36042 DBCC TRACEON(3604)3 GO4 5 -- Dump out a page somewhere in the data file6 -- A hex dump is working here7 DBCC PAGE (TestDatabase, 1, 1000, 2)8 GO

你看到我在我的資料檔案裡隨便傾倒出了一個頁。在那個情況下，SQL Server現在就會返回你一些垃圾資料——在新分配的NTFS簇裡先前儲存的資料——對SQL Server毫無關聯的資料。

通過對SQL Server授予這個許可權，基本上你開啟了一個安全性漏洞：使用者（有正確許可權的）可以獲得老資料，在檔案系統裡先前儲存的。因此對此你必須要仔細考慮下，對SQL Server是否要授予這個特權。

如果你想知道，你的SQL Server是否帶這個許可權在運行，你可以啟用3004和3605跟蹤標記。用這些啟用的跟蹤標記，SQL Server在錯誤記錄檔會報告那個檔案被填0初始化。接下來當你建立新的資料庫時，對於SQL Server沒有授予這個特權，從錯誤記錄檔你可以看到，資料和記錄檔都被填0初始化了：

如果SQL Server有執行卷維護任務（Performance Volume Maintenance Task）特權，從錯誤記錄檔你可以看到，只有記錄檔被填0初始化：

 

Windows核心

在SQL Server運行下服務帳號，你對它授予了執行卷維護任務（Performance Volume Maintenance Task）特權，在Windows作業系統內部會發生什麼呢？啟用這個特權後（它內部是通過WIN32 API調用所謂的SE_MANAGE_VOLUME_NAME函數），SQL Server可以調用SetFileValidData的WIN32 API函數。從線上文檔裡可以看到，調用那個函數的進程，有SE_MANAGE_VOLUME_NAME許可權。當那個函數被SQL Server調用時，函數本身會設定檔案所謂的High Watermark——在其NTFS簇裡，檔案直接擴充而不重寫原始內容！正如線上文檔所述：

 

“The SetFileValidData function allows you to avoid filling data with zeros when writing nonsequentially to a file. The function makes the data in the file valid without writing to the file. As a result, although some performance gain may be realized, existing data on disk from previously existing files can inadvertently become available to unintended readers.”

 

…

 

“If SetFileValidData is used on a file, the potential performance gain is obtained by not filling the allocated clusters for the file with zeros. Therefore, reading from the file will return whatever the allocated clusters contain, potentially content from other users. This is not necessarily a security issue at this point, because the caller needs to have SE_MANAGE_VOLUME_NAME privilege for SetFileValidData to succeed, and all data on disk can be read by such users.”

 

我已經說過，對於你的SQL Server執行個體，你是否啟用這個許可權主要是關係到安全的。

小結

對於你的SQL Server執行個體，你是否應該啟用檔案立即初始化？這個依具體情況而定……當你是SQL Server和系統管理員時，授予這個許可權是個好主意，因為作為系統管理員，你總是可以訪問檔案系統的。但當你有專屬的系統管理員和SQL Server管理員時，這就不可能授予了，因為系統管理員並不信任你，對於你的SQL Server執行個體你不會獲得這個許可權。那樣的話SQL Server總會填0初始化資料和記錄檔……

感謝關注！

使用檔案立即初始化提高SQL Server效能