[SQL Server]改善I/O效能- NTFS Allocation Unit Size(4KB vs 64KB)

2016-09-18

7717
0
SQLInstall
2016-10-15

SQL Server安裝在Windows OS中，而存放Data的磁碟幾乎都格式化為NTFS檔案系統，預設磁碟分割大小則是4KB。

從Super SQL Server社群楊老師的文章得到收穫和啟發，NTFS檔案系統預設的4KB可能不是SQL Server Data/Log Page的存取最佳值，來試試看其他Allocation Unit Size對效能的幫助。

NTFS Allocation Unit Size(4KB VS 64KB)

1.首先確認測試磁碟機D: 的相關資訊
這邊用上了內建在Windows中的檔案系統管理工具FSutil.exe

FSutil FSinfo NTFSinfo D:

可惡!果然是預設的4KB!

新增一個txt檔案也可以發現，檔案大小1.87KB，但磁碟大小是4KB。

2.建立測試資料庫及測試資料表
將資料及log檔都放置在這顆硬碟，同時資料庫的復原模式設置為full!

CREATE DATABASE [Test4K]
CONTAINMENT = NONE 
 ON PRIMARY 
( NAME = N'Test4K', FILENAME = N'D:\Test\Test4K.mdf' , SIZE = 1024000KB , FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'Test4K_log', FILENAME = N'D:\Test\Test4K_log.ldf' , SIZE = 102400KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Test4K] SET RECOVERY FULL 
GO

USE [Test4K]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Test4K] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

建立一個測試資料表

CREATE TABLE T1(C1 CHAR(10),C2 CHAR(10), C3 CHAR(10),C4 DATETIME)
GO

3.執行大量的寫入及查詢。

INSERT T1(C1,C2,C3,C4) values('C1 Value','C2 Value','C3 Value',GETDATE())
go
INSERT T1(C1,C2,C3,C4) select C1,C2,C3, GETDATE() from T1
go 24

DECLARE @END DATETIME = GETDATE();
DECLARE @START DATETIME
SET @START = (SELECT TOP 1 C4 FROM T1)
PRINT 'execution time(millisecond):' + CONVERT(VARCHAR, DATEDIFF(ms, @START, @END))

預設格式化大小為4K的3次結果

execution time(millisecond):69.860秒

execution time(millisecond):72.580秒

execution time(millisecond):70.630秒

4.接著將磁碟格式化為64k

按一下開始，輸入diskmgmt.msc啟動磁碟管理

配置大小為64K

記得!!!不要把有資料的磁碟直接作格式化喔!(如果按下去，磁碟內的資料就會被清除了!)

確認磁碟機資訊

FSutil FSinfo NTFSinfo D:

新增一個txt檔案觀察: 檔案大小1.75KB，磁碟大小64KB

1.75KB占用64KB磁碟大小

5.先Truncate Table後，重新執行步驟3

格式化大小為64K的3次結果:

execution time(millisecond):67.226秒

execution time(millisecond):65.333秒

execution time(millisecond):66.536秒

大概有個10%執行時間的節省，Good!可能我們在VM環境，效果沒有這麼明顯。

在以前寫Cobol的年代，IBM主機的檔案系統VSAM或是SAM也有類似的CI Size/Block Size，也許需要再進一步找時間在其他環境測試看看。

SQL Server Data and Log Page I/O Block Size

想到曾聽百敬老師課堂解釋過SQL Server在處理Data Page及log從Buffer寫入硬碟的方式，我們來用Process monitor監測實際I/O磁碟的Block size!

1.log buffer flush到Disk就可能是因為transaction commit而觸發，或是log buffer 到達60K的限制(不是64KB)。

2.Data Page Buffer flush到Disk，通常是Checkpoint或是 lazy writer時處理，從我們製作出來的SQL I/O情境觀察，I/O Block size從8KB-256KB都有!

3.現在試讀取的行為上:

哈哈!Bingo!

這一點和我們之前學到的1 Page Size = 8K；8個Page = 1 Extent = 64KB，然後SQL Server 用1個Extent為單位讀取資料相同。

整理SQL Server I/O Data 及 Log Page Block size:

I/O Type	I/O Block size
Log Page Write	~60KB
Data Page Write	8KB-256KB
Data Page Read	64KB

Other NTFS Allocation Unit Size(32KB)

不過，既然I/O Block size不一定是64KB，如果要兼顧讀取和寫入效能，如果稍微小一點有沒有幫助?

找到一篇Disk Partition Alignment Best Practices for SQL Server，裡面有一段我們把他節錄出來。

https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

An appropriate value for most installations should be 65,536 bytes (that is, 64 KB) for partitions on which SQL Server data or log files reside. In many cases, this is the same size for Analysis Services data or log files, but there are times where 32 KB provides better performance. To determine the right size, you will need to do performance testing with your workload comparing the two different block sizes.

將NTFS 格式化為64 KB是SQL Server安裝時放置data及log磁碟格式的適當選擇，但某些SSAS案例中，32KB也許可以提供更好的效能!