SQL Server IO 子系統淺究 I

作者：obuntu

很喜歡SQL Server裡面4步定位效能問題的理論，具體如下：
1，資源瓶頸
i. 記憶體 ii. CPU iii. IO
2, Tempdb瓶頸
3，找出執行慢的語句，可以通過三個方面來尋找
i. 統計資訊 ii. 缺失索引 iii. 阻塞
4，緩衝執行計畫分析

更多資訊，可以參看此篇文章http://blogs.msdn.com/b/jimmymay/archive/2008/09/01/sql-server-performance-troubleshooting-methodology.aspx

可以看到，一遇到系統效能問題時，第一步是確定資源是否存在瓶頸，在CPU,記憶體，IO 三者之間，最容易形成瓶頸的是IO子系統。其實IO子系統的內涵是很深的，能夠影響IO子系統效能的因素有磁碟的數目，大小，和轉速；檔案配置單位大小(file allocation unit size)；HBA；網路頻寬；磁碟緩衝；控制器；是否使用SAN（storage area networks）；RAID層級；匯流排速度；IO通道等等。
作為SQL Server的使用者，通常很少會去調整IO子系統的配置，一則重視不夠，二是缺少這方面的相關知識和技能。但瞭解這方面的相關問題還是很有必要的，除了可以更好發揮硬體的作用外，在碰到系統效能問題時，也能很好的進行定位分析。

目錄
IO子系統相關概念
SQL Server IO 相關概念
效能監控器裡的IO子系統計數器
SQLIO
關於IO的一些最佳實務
小結
參考資料

IO子系統相關概念

A，磁碟

磁碟從過去的幾十年裡，取得了快速的發展，從ATA,SATA,SAS,到現在的SSD，每次技術的變革都帶來了磁碟的效能提升。現在應用最廣泛的應該是15K轉的SAS盤了，對於這樣的盤，一些傳統的磁碟概念還是不變，如磁軌，扇區。

現在的硬碟，一般是由重疊的一組碟片組成，每個碟片又被劃分為數目相等的磁軌，同時對這些磁軌進行編號。每個磁軌被等分為若干個弧段，這些弧段就是扇區，扇區的一般為512bytes，也有1K，2K，4K大小。同時不同碟片上，相同編號的磁碟則組成柱面。柱面數等於磁軌數，盤面數等於總的磁頭數，因此硬碟上有所謂的CHS概念，即Cylinder（柱面）、Head（磁頭）、Sector（扇區）。磁碟的容量等於柱面數*磁頭數*扇區數*扇區的大小。

IO的模式有順序讀寫和隨機讀寫，磁碟在處理這2種讀寫方式時，所表現出來的效能是不一樣的。一般來說，在順序讀寫上，現在10K轉的磁碟，能夠擷取40mb/s~80mb/s的傳輸速率；15K轉的磁碟，能夠擷取70mb/s~125mb/s的傳輸速率。對於隨機讀寫來說，其效能取決於磁碟的轉速和尋道時間。一個10K轉的磁碟完成一個完全的旋轉需要6ms（1*60*1000/10000）。硬碟的等待時間，又叫潛伏期(Latency)，是指磁頭已處於要訪問的磁軌，等待所要訪問的扇區旋轉至磁頭下方的時間。平均等待時間為碟片旋轉一周所需的時間的一半，一般應在4ms以下，所以一般認為在磁碟上的等待時間為3ms，對15K的磁碟則是2ms。

還有一個影響磁碟效能的因素是尋道時間，它是指硬碟在接收到系統指令後，磁頭從開始移動到移動至資料所在的磁軌所花費時間的平均值。現在10K轉的磁碟在讀上，平均的尋道時間為4.6ms，在寫上，平均的尋道時間為5.2ms。一個15K轉的磁碟，讀的平均尋道時間為3.5ms，寫的尋道時間為4.2ms。

如對於小塊的8kb讀取，傳輸的時間大概為0.1ms，忽略其他可以忽略的因素後，結合上述討論，對於隨機讀，我們可以得出總的時延大概為8ms（10K磁碟）和5.6ms(15K轉磁碟)，所以磁碟在一般隨機的小塊讀的效能大概為 125 IOPS（10K磁碟）和175 IOPS（15K磁碟）。

上述的情況是較為理想的。如果磁碟上的資料集中在某塊小的地區，會降低磁碟的平均尋道時間，效能還會更好。但如果多個IO請求同時發生的話，磁碟還需要對多個IO進行序列化，排序，從而在越高的吞吐下，其時延會更長。一般來說，如果資料分佈於整個磁碟，隊列深度(queue depth)越高，時延更長，隊列深度為4時，時延會達到20ms，隊列深度為32時，時延能達到100ms。隊列深度指的是磁碟上能並行啟動並執行IO個數。 因此，對於隊列深度的值建議使用2，當然不同的儲存，不同的系統也會有不同的建議值，設定的時候可以參考相關的資料。但也有個情況需要注意，那就是如果資料只分佈於磁碟上的某一小塊地區，如5%，那麼時延並不會隨著隊列深度的增加而大幅增加，一般是隊列深度為8時20ms，隊列深度為16時40ms，而且隨機讀的效能也有很大的提升，每個IOPS可以達到400.這個特點在處理強事務的能讓你獲得很大的彈性空間。

B，RAID

現在真實的公司專屬應用程式環境很少單獨使用一個一個磁碟來存放檔案，而是採用RAID技術。RAID技術能帶來效能的提升和有效容錯能力。簡單的說，RAID是一種把多塊獨立的硬碟（物理硬碟）按不同的方式組合起來形成一個硬碟組（邏輯硬碟），從而提供比單個硬碟更高的儲存效能和提供資料備份技術。組成磁碟陣列的不同方式稱為RAID層級（RAID Levels）。

RAID 有很多種層級，也就是說磁碟有多種組合方式。現在比較常用的是RAID10和RAID5，RAID10的整體效能會比RAID5來得高，但其價格也更昂貴。決定使用哪種RAID層級，對系統的效能影響也很大，因此需要經過充分測試，權衡自己的實際情況並作出選擇。

C，其他概念

檔案配置單位(file allocation unit)大小，也就是簇的大小，一般是扇區大小的整數倍，如簇的大小是4K，扇區的大小為512bytes，那麼一個簇就會使用到8個扇區。在進行硬碟格式化時，可以使用format命令的/A:size 選項來指定。一般情況下，在SQL Server資料檔案和記錄檔上比較合適的大小是64K，但有時候32K也能提供較好的效能，因此設定該值之前，最好也進行充分的測試來決定。下面是一個查看當前檔案配置單位大小的例子。
C:\Documents and Settings\Administrator>fsutil fsinfo ntfsinfo d:
NTFS 卷序號 : 0xde500ef9500ed7e3
版本 : 3.1
區數量 : 0x0000000012c03620
簇總數 : 0x0000000012c03620
可用簇 : 0x000000001098efb6
保留總數 : 0x0000000000000000
每個扇區位元組數 : 512
每個簇位元組數 : 512
每個 FileRecord 段的位元組數 : 1024
每個 FileRecord 段的簇數 : 2
Mft 有效資料長度 : 0x0000000004a68000
Mft 起始 Lcn : 0x0000000000600000
Mft2 起始 Lcn : 0x0000000009601b10
Mft 地區起始 : 0x0000000000625460
Mft 地區結尾 : 0x0000000002b80800

配置RAID的時候，有個可以手工設定的參數：Stripe size. 邏輯磁碟機的Stripe size，代表控制器每次寫入一塊物理磁碟的資料量，以KB為單位。 不同Stripe size的選擇直接影響效能，如IOPS和輸送量。 Stripe size值小，通過多塊磁碟響應多個I/O請求，可以增加I/O訪問速率（IOPS）；Stripe size值大，通過多塊磁碟響應一個I/O請求，可以增加資料轉送速率（Mbps).為了獲得更高的效能，要選擇條帶的容量等於或小於作業系統的簇的大小。大容量的條帶會產生更高的讀取效能，尤其在讀取連續資料的時候。而讀取隨機資料的時候，最好設定條帶的容量小一點。

因此，可以看到上述值得設定對SQL Server的效能也是有協助的，但很難有一個合適的推薦，有時候大部分還是保持預設的，如果確實遇到這方面的設定需求，最好請教相關產品的廠商，或者自己進行充分測試。

SQL Server IO 相關概念

SQL Server 引擎有自己的磁碟IO內部管理機制。理解SQL Server的IO處理機制是很有必要的。微軟有2部非常好的白皮書，叫《SQL Server I/O Basics Chapter 1》《SQL Server I/O Basics Chapter 2》，對此進行了深入闡述，如果對這方面有興趣的朋友，是不能錯過。但只有英文版，兩份加起來有100多頁左右。下面對SQL Server IO的一些要點進行簡要闡述，更多詳情，參考這兩份白皮書吧。

Write Ahead Logging (WAL) Protocol

SQL Server在寫入資料檔案的資料時，需要事先將記錄檔的內容寫入磁碟上的交易記錄檔，這就是WAL機制。這個機制可以保護和固化所進行的事務。只有這樣，才能實現事務的durability 特性。SQL Server實現WAL機制是通過使用Createfile 的FILE_FLAG_WRITE_THROUGH標識來實現的。

Synchronous vs Asynchronous I/O

同步I/O指的是I/O API 會等待I/O請求完成後，才進行下一個處理；非同步I/O指的是I/O API只需發出I/O請求，然後繼續處理其他內容，並在一會之後回頭查看該I/O是否已經完成。

SQL Server 上98%使用的是非同步I/O，這允許SQL Server在寫入或者讀取一個頁之後繼續有效使用CPU和其他資源。Windows平台處理非同步I/O是使用了OVERLAPPED這個結構來儲存相關的I/O資訊，並使用HasOverlappedIOCompleted來標識I/O是否已經完成。在SQL Server 2005後引入了sys.dm_io_pending_io_requests 這個動態管理檢視，其中的IO_PENDING列與HasOverlappedIOCompleted對應。

Scatter / Gather I/O

在SQL Server 2000以前，SQL Server的checkpoint要將buffer pool的髒資料頁寫入磁碟時，需要維護一個髒資料頁的列表，然後按順序的寫入髒資料頁，因此如果某個頁在寫入時遇到I/O問題，則會引起整個checkpoint的效能下降。因此SQL Server 2000以後引入了Scatter/Gather I/O的方式，Scatter 是指從磁碟往記憶體讀取資料頁時，不用在記憶體配置連續的頁，可以將頁分布在buffer pool裡不同的地方，通過調用ReadFileScatter這個API來實現的；Gather指的是從記憶體往磁碟寫資料頁時，不必維護之前的那種髒資料頁列表，而是在掃描整個buffer
pool後，直接將髒資料頁寫入磁碟某塊連續地區，通過調用WriteFileGather這個API來實現。很明顯可以看到這種處理方式更為有效，不僅應用在SQL I/O路徑上，還應用在Page File 上。

Sector alignment, Block Alignment

在SQL Server裡面，寫入交易記錄時，並不是按照page的大小（8KB）來寫入的，而是按照扇區的大小來寫入的。之所以採用扇區來寫入是為了防止交易記錄被re-write，從而導致交易記錄損壞。在扇區上會維護一個校正位，在寫入記錄檔時，通過檢查該檢驗位來確定該扇區是否可以寫入日誌，從而保證日誌的有效性。

扇區大小對使用者而言其實是透明的，也就是SQL Server會自動根據磁碟的扇區大小作出相關處理，例如從一個扇區512bytes的還原到扇區為1024bytes時，後續的日誌寫入就是按照1024bytes了。

由於塊的最小單位是8KB，又因為在一個磁碟，預設情況下前63個扇區為隱藏扇區，用來儲存MBR（主引導資訊），也就是隱藏的扇區大小為31.5KB。這個東西叫做分區位移，如果未進行有效設定時，會導致額外的I/O產生，進而影響效能。這個問題，後續我們會進行詳細展示。

一般來說，確定合適的扇區大小，可以通過一個計算公式來進行，((Partition offset) * (Disk sector size)) / (Stripe unit size) ，確保結果為整數。例如在一個stripe size 為256的情況下，至少要在512個扇區的位移後，才能保證公式結果為整數，因此至少需要設定256KB的位移大小。
(63 * 512) / 262144 = 0.123046875
(64 * 512) / 262144 = 0.125
(128 * 512) / 262144 = 0.25
(256 * 512) / 262144 = 0.5
(512 * 512) / 262144 = 1

要查看一個檔案上的扇區大小，還可以使用dbcc fileheader(‘dbname’)來查看。

Latching and a page: A read walk-through

Latch，是種輕量級的鎖，用來保護各種系統資源，在I/O上則是用來保護記憶體中的資料頁，保證資料的一致性。在SQL Server裡，有2類IO方面的latch，一種是PAGE_IO*_LATCH，一種是PAGE*_LATCH，這兩類等待類型能夠用來定位I/O和記憶體方面的問題。同時與鎖一樣，latch也有SH（共用）和EX（排他）這樣的性質。

PAGE_IO*_LATCH用於讀取或者寫入page時，如果讀寫期間過長，則這類等待就會很明顯。例如對於從物理檔案讀取一個page時，就會請求一個EX的latch，直到這個讀取完成後才釋放，這樣就能保證讀取的過程中，不會被其他的修改。而PAGE*_LATCH則是對已經存在於記憶體中的page進行加latch，也是在需要的時候才添加latch。SH類型的latch不會阻塞SH類型的latch，但會阻塞EX類型的latch。

同時，需要注意latch只在user mode下發生，在kernel mode中對資源的競爭管理是由SQLOS來負責的，

在latch上，為了減少熱點頁的競爭(hot page)，SQL Server還引入了sub-latch的機制。Sub-latch只發生在已經存在記憶體中的頁上。例如，當SQL Server檢測到在持續的一段時間內，有很高的SH 行為的latch發生，會將已經持有的latch提升為sub-latch，sub-latch是將一個latch根據邏輯CPU分為多個對應的latch結構隊列，這樣worker只需要為本地調度器請求一個SH的sub-latch，這樣可以避免連鎖活動，也使用了更少的資源，提高了處理hot page的能力。當然，這一切是SQL
Server自動發生的，不需要我們進行幹預。

Reading Page

當CPU的某一worker thread需要請求一個page時，會調用BufferPool::GetPage模組，GetPage函數會對BUF結構進行掃描，如果發現請求的page，就會對該page添加latch並返回給調用者；如果沒有發現，則需要從磁碟讀取該page。

讀取page時，會有多種行為，如預讀（read ahead）機制，但基本步驟如下：
步驟1：向記憶體管理器(memory manager)發出一個請求分配固定大小的page；
步驟2：該page會與一個跟蹤該page的BUF結構關聯；
步驟3：在該page上添加EX latch來防止被修改；
步驟4：將BUF結構插入記憶體中的一個HASH 表。這樣所有使用到同樣BUF和PAGE的請求會受到EX latch的保護。如果相關對象已經在HASH表中，則不需要這一步，而是直接去HASH表擷取相關內容；
步驟5：建立I/O請求，並發送該I/O請求（非同步I/O）
步驟6：嘗試去擷取已請求的latch類型；
步驟7：檢查各項錯誤條件，如果有錯誤則拋出錯誤。

如果有錯誤產生時，會導致其他活動的發生，例如如果checksum校正失敗，就會產生re-read（重讀）的行為。從上述步驟可以看出，當讀取Page完成後，並不會立即釋放相關的EX latch，而會等到頁校正完成後才釋放。

Writing Page

寫page時與讀page是十分相似的。寫page時都是針對page已存在於記憶體中，並且BUF的狀態被標記為dirty（已改變），要看髒頁，可以通過sys.dm_os_buffer_descriptors來查看。寫入page時，SQL Server是調用WriteMultiple來完成的。寫page時，涉及到了三個thread，分別是lazywriter，checkpoint，eager write。

Lazywriter是一個定期掃描buffer pool來檢查free list 大小的線程。在SQL Server 2008後，引入了TLA(TIME LAST ACCESS)演算法，這個演算法是對LRU的改進。Lazywriter根據該演算法對標記為髒頁的頁進行判斷，如果已經過時(aged)，則調用WriteMultiple將相關dirty page寫入磁碟。

Checkpoint 是用來標識所有已提交的事務所關聯的changed page 是否已經被寫入磁碟。Checkpoint是recovery的開始點。與lazywriter不同的是，checkpoint並不會將dirty page從緩衝中移除，而是將其標記為clean（乾淨的）。有很多條件會觸發checkpoint，在checkpoint發生時，就會調用WriteMultiple完成相關寫入。

Eager write 在一些BCP，blob欄位的操作中，有些頁必須從記憶體中寫入到磁碟才能完成相關的事務，這種寫就是eager write，同樣是通過調用WriteMultiple來完成的。

在寫入請求的頁時，不僅僅會請求寫入髒頁，還會寫入鄰近的頁，減少I/O請求，提高I/O效能。寫入頁，同樣需要latch的支援，一般是請求EX，防止未來可能的頁修改。但SQL Server 也能允許在寫入頁的過程中使用SH latch來讀取相關的內容。

PAE and AWE

這個就不用多說，但有兩點需要注意：一是PAE和AWE是獨立的，開啟AWE，不需要PAE；開啟PAE，也不需要AWE；二是AWE只擴充buffer pool的大小，對plan cache等不進行擴充。

Read Ahead

如果我們開啟set statistics io on時，經常會看到預讀多少的內容。SQL Server的預讀機制可以大大提高非同步I/O能力。

Sparse Files and Copy On Write (COW) Pages

疏鬆檔案主要用於線上DBCC和快照資料庫中。疏鬆檔案一般情況下實際佔用空間遠小於檔案大小值。在建立snapshot資料庫時，會伴隨著copy on write的行為，copy on write 指的是當一個頁將要被寫入內容時，會發生一個檢查，確定該頁是否被copy到了snapshot資料庫，如果沒有，則在該頁被改變之前會被寫入到snapshot資料庫上，從而保證snapshot的內容一直為某一時刻的。為了維護snapshot資料，在parent庫上會有file control block chaining（FBCs）來管理snapshot與parent的對應關係，這樣copy
on write就能快速定位。

Snapshot雖然開始很小，但隨著parent庫的更改，會慢慢變大，因此在建立snapshot資料庫時，需要考慮到這一點。又由於可能需要與資料檔案頻繁的互動，因此還需要將snapshot放在I/O效能好的裝置上。

當在snapshot資料庫進行查詢時，讀取請求會先發生在snapshot資料庫上，如果相關的page還沒從parent庫拷貝過來時，就將該讀取請求發送給parent上的FBC，並從parent庫上讀取相關的page。這樣能很大程度的保證了snapshot的稀疏。

DBCC 同樣會使用快照來完成相關內容，當然這個快照是內部維護的。這個概念也澄清了一個誤區“DBCC CHECKDB會對資料庫裡的page加鎖”。事實上，在2005以後，DBCC就通過維護內部快照從而完成對資料庫的一致性檢查。當然，這對I/O的要求比較高，並需要有較多的空間，如果不滿足條件的話，可以使用WITH TABLE LOCK 直接在資料庫檔案上進行一致性檢查。

Scribbler(s)
Scribbler寓意小孩在圖片的線框外亂塗顏色，表示一個組件在記憶體中的不屬於它的地區改變資料。這會造成資料損毀。在SQL Server 2000中，為了防止這種行為的出現，引入了Torn page的校正機制；而在sql server 2005後，還引入checksum機制。

如果page_audit設定為checksum時，lazywriter會檢查記憶體中的頁，並重新計算頁上的checksum值，如果值不一致，就會紀錄錯誤並將該頁直接從記憶體中消除，這就表明發生了一次“scribbler”。追蹤頁“scribbler”是比較困難的，但有trace flag –T831 可以開啟，從而擷取更詳細的內容。

頁的校正是SQL Server IO上很重要的一個內容，更多內容可以參考SQL Server IO basic 這份白皮書。

可以看到SQL Server 提供了豐富的內部I/O管理機制。理解這些概念，也就能更好的理解SQL Server的工作機制，在碰上一些內部錯誤或者I/O子系統設定時便能應付自如。更多詳情請參閱SQL Server IO basic 白皮書。