SQL Server IO 子系統淺究 II

效能監控器裡的IO子系統計數器

在運行裡面輸入”perfmon”，使用效能監控器，可以觀察當前的I/O效能情況，確定是否存在I/O方面的問題。

在SQL Server上，涉及I/O的計數器有：
Average read bytes/sec,平均每秒的讀取位元組數
Average write bytes/sec，平均每秒的寫入位元組數
Reads/sec, 每秒多少讀
writes/sec，每秒多少寫
Disk read bytes/sec, 每秒讀取的傳輸速率
Disk write bytes/sec，每秒寫入的傳輸速率
Average disk sec/read, 平均每個讀花的時間，小於10ms表示效能很好，在10ms~20ms之間表示效能可以接受，如果大於20ms，說明存在I/O問題；
Average disk sec/write，平均每個寫花的時間，小於10ms表示效能很好，在10ms~20ms之間表示效能可以接受，如果大於20ms，說明存在I/O問題。
Average disk queue length，在磁碟等待的IO個數，最好在1.5和2倍轉軸個數(spindles)以內，如果高於這個值，一般說明IO子系統有一定的問題，但現在普遍使用RAID或者磁碟虛擬化，如果不詳細瞭解具體的I/O子系統配置，很難從這個計數器判斷出問題。

除了使用perfmon外，還可以使用SQLDiag.exe或Perfstats指令碼來擷取整體系統的效能情況，然後通過PAL分析採集的perfmon檔案，具體詳情就google下吧。

SQLIO
前面也提到過，使用I/O子系統時，最好能夠進行充分測試。SQLIO就是這樣一款類比SQL Server讀寫特點的效能測試工具，使用SQLIO可以快速定位I/O子系統的瓶頸所在，對如何分布SQL Server檔案也會有更好的協助。下面簡要介紹下SQLIO的使用。

SQLIO的為http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en 。安裝完成後，有個param.txt的設定檔，裡面的內容為：c:\testfile.dat 2 0x0 100 ，這邊我們只需要關注第一個參數和最後一個參數。
第一個參數表示測試檔案的所在目錄，根據實際要測的硬碟來改變盤符；
最後一個參數表示測試檔案的大小，以MB為單位，一般盡量與實際的資料庫大小一致。配置完成後，啟用cmd命令列工具，到SQLIO的安裝目錄下，運行如下命令來產生測試檔案。

SQL code

sqlio -kW -s10 -fsequential -o8 -b8 -LS -Fparam.txt timeout /T 10

接著就可以使用SQLIO來進行測試了。一般的測試語句形式如下：

SQL code

sqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat

其中
參數 –kW 表示類比寫的情況，如果是-kR則表示寫；
參數 –t2 表示兩個線程
參數 –s120表示測試2分鐘
參數 –dM 表示具體的盤符
參數 –o1表示outstanding IO的數目
參數 –frandom表示隨機模式，相反的是sequential 序列模式
參數 –b64 表示每個IO大小為64K

可以將這樣的命令儲存為一個批次檔，例如SQLIOBatch.bat，然後在cmd下，使用SQLIOBatch.bat >SQLIOResult.txt 將測試結果儲存為txt檔案，然後再去分析。SQLIO運行後的結果如下：
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
16 threads writing for 60 secs to file d:\MSSQL\testfile1.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 2048 MB for file: d:\MSSQL\testfile1.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 18527.91
MBs/sec: 144.74
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 6
Max_Latency(ms): 4900
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 78 6 8 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2

其中比較關鍵的指標就是IOs/sec 和MBs/sec了。

這邊順便介紹一個分析SQLIO結果的Powershell指令碼，具體參看“http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/25/parsing-sqlio-output-to-excel-charts-using-regex-in-powershell.aspx”，很強大的。

關於SQLIO 的使用，可以參考Brent Ozar在SQLServerPedia上的文章，裡面也有詳細的SQLIO測試指令碼。http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO

關於IO的一些最佳實務
儘管IO子系統比較複雜，但還是有一些經過總結的良好實踐，值得借鑒。

A， 使用多個資料檔案

使用多個資料檔案可以提高效能，但這也是需要看情況而定的。Paul Randal 在他的blog上曾經貼出了一篇使用多個資料檔案的測試文章。測試的結果就是隨著資料檔案的增加，效能會先提高，到一個值後，會繼續下降。如

可以看到，在8個資料檔案的情況下，其效能會有很大的提升。

具體詳情，請參考http://sqlskills.com/BLOGS/PAUL/category/IO-Subsystems.aspx

B， 設定合理的磁碟分割位移(disk partition alignment)

這個問題最早是由Jimmy May（http://blogs.msdn.com/b/jimmymay/）提出的。在前面講扇區的時候，我們也提到在windows 2003以前，預設會將磁碟前面的63個扇區設為隱藏扇區，用來存放MBR的，這樣一來，就使得檔案配置單位的大小是在31.5K之後開始的，從而將本來一個I/O的操作，變成了2個I/O。通過設定該參數，可以有效提高效能20%~30%，自己也親自測過這個調整的參數值，也確實有這樣的效果。

要查看自己當前的disk partition alignment值，可以使用
wmic partition get BlockSize, StartingOffset, Name, Index
擷取的結果類似如下：
BlockSize Index Name StartingOffse
512 0 磁碟 #0，分區 #0 32256
512 1 磁碟 #0，分區 #1 48322068480

32256/124=31.5，就是一個典型的未設定合理值的例子。在windows 2008以後，在初始化時，已經對這個值進行修改了，因此不會存在這樣的問題。但2003 和XP 還是需要進行手工設定，設定的時候通過diskpart來進行，但需要注意的是，設定這個值後，需要對磁碟進行重新格式化才能使用！！

C:\>diskpart
Microsoft DiskPart version 6.0.6001
Copyright (C) 1999-2007 Microsoft Corporation.
On computer: ASPIRINGGEEK
DISKPART> list disk
Disk ### Status Size Free Dyn GPT
-------- ---------- ------- ------- --- ---
Disk 0 Online 186 GB 0 B
Disk 1 Online 100 GB 0 B
Disk 2 Online 120 GB 0 B
Disk 3 Online 150 GB 150 GB
DISKPART> select disk 3
Disk 3 is now the selected disk.
DISKPART> create partition primary align=1024
DiskPart succeeded in creating the specified partition.
DISKPART> assign letter=F
DiskPart successfully assigned the drive letter or mount point.
DISKPART> format fs=ntfs unit=64K label="MyFastDisk" nowait

更多詳情，可以參考白皮書《Disk Partition Alignment Best Practices for SQL Server》http://msdn.microsoft.com/en-us/library/dd758814.aspx

C， 其他最佳實務

有很多這樣的文章，如
《Storage Top 10 Best Practices》http://msdn.microsoft.com/en-us/library/cc966534.aspx

《Physical Database Storage Design》http://technet.microsoft.com/en-us/library/cc966414.aspx
裡面會有一些關於I/O方面的設定指南，值得參考。

小結
通過對IO子系統的學習研究，發現其涉及的內容實在廣泛，這邊只是介紹了一些自己覺得比較重要的東西，而且可能有些方面還值得推敲。歡迎大家一起探討。

參考資料
SQL Server I/O Basics Chapter 1
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

SQL Server I/O Basics Chapter 2
http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx
Storage Performance for SQL Server
http://sqlblog.com/blogs/joe_chang/archive/2008/03/04/storage-performance-for-sql-server.aspx