【轉】SQLSERVER磁碟原理

標籤：指標   計劃   高效   緩衝   lib   opera   資料頁   翻譯   level   

　　【聲明：本篇部落格轉載自http://www.cnblogs.com/ljhdo/p/5149401.html】

　　最近一段時間的工作主要是與SQLSERVER資料庫打交道，需要對SQLSERVER有一個比較全面的認識。故也在捉急的翻閱資料，學習這個經典的關係型資料庫。

效能計數器（Performance Counter）是量化系統狀態或活動的一個數值，Windows Performance Monitor在一定時間間隔內（預設的取樣間隔是15s）擷取Performance Counter的當前值，並記錄在Data Collections中，通過Performance Monitor能夠查看系統的效能資料，是故障排除的極佳工具。Performance Counter數量很多，如果不瞭解計數器的功能，在選擇計數器時，往往不知所措。由於SQL Server 是IO密集型的應用程式，經常需要進行大量的讀寫操作，從Disk讀取資料到記憶體，將記憶體中的資料寫入到Disk，因此，Disk和記憶體是SQL Server的生命線，監控SQL Server 的效能，經常用到的效能計數器是Disk和記憶體。

一，Disk效能監控

1，Disk的結構

典型的機械Disk的結構主要有：磁頭（head），磁軌（track），扇區（sector），盤面（Platter），柱面（cylinder）和簇（cluster）。，

當磁碟旋轉時，若磁頭保持在一個位置上，則每個磁頭都會在磁碟表面划出一個圓形軌跡，這些圓形軌跡叫做磁軌。磁碟上的每個磁軌被等分為若干個弧段，這些弧段是磁碟的扇區，每個磁軌上的扇區數量是相等的，每個扇區存放512個位元組的資訊，磁碟機在向磁碟讀取和寫入資料時，以扇區為單位。若干個連續的扇區組合為一個簇，檔案存取是以簇為單位的。

硬碟通常由重疊的一組碟片構成，每個盤面都被劃分為數目相等的磁軌，並從外緣的"0"向中心開始編號，具有相同編號的磁軌形成一個圓柱，稱之為磁碟的柱面。磁碟的柱面數與一個盤面上的磁軌數是相等的。由於每個盤面都有自己的磁頭，因此，盤面數等於總的磁頭數。所謂硬碟的CHS，是指Cylinder（柱面）、Head（磁頭）、Sector（扇區），硬碟的容量=柱面數×磁頭數×扇區數×512B。

扇區是能獨立定址的最小單位，簇是資源分派的最小單位。Disk的一次讀寫操作，由尋道，旋轉延遲和資料轉送組成，由於尋道和旋轉延遲佔用了讀寫操作的大部分時間，Disk在執行每次讀寫操作時，採取就近原則，讀寫連續的N個扇區，讀寫的資料量是4KB的整數倍。

2，順序讀寫和隨機讀寫

隨機讀寫是指資料分布在不同的磁軌上，Disk的磁頭必須移動磁軌，才能讀取到相應的資料；順序讀寫是指資料分布在同一磁軌的相鄰扇區中，在讀寫資料時，Disk的磁頭不需要移動磁軌。由於，Disk的磁軌移動是機械運動，“很慢”，占讀寫資料所用時間的絕大部分，因此，Disk的順序讀寫速度遠遠高於隨機讀寫速速，應盡量避免隨機讀寫。Disk，固態硬碟盒記憶體的讀寫速度對比：

3，硬碟效能計數器

在OS Level上，Windows在一塊物理硬碟上分成多個邏輯分區，每一個邏輯分區叫做一個Logical Disk，通過盤符標識，運行在Windows上的Application使用盤符來定址。對於分配在同一塊物理硬碟上的邏輯分區，共用物理硬碟的讀寫頻寬，相當於在一塊物理硬碟上工作。因此，Disk計數器分為兩組：PhysicalDisk 和 LogicalDisk，LogcialDisk計數器記錄每個邏輯分區的讀寫計數，用於分析特定的Application在不同的邏輯分區上的Disk IO活動和績效參數；PhysicalDisk計數器記錄整個物理Disk的效能指標，用於瞭解Disk的響應速度，主要使用PhysicalDisk計數器，分析Disk的效能問題。

系統級經常用到的Disk效能計數器是PhysicalDisk計數器，LogcialDisk僅供參考：

• %Disk Time ：表示Disk的忙碌程度，是Disk處理讀寫請求的時間的百分比，經常大於100%，建議使用%IdleTime反推出Disk處於讀寫狀態的百分比
• Disk Reads/sec ：每秒向Disk請求讀操作的次數
• Disk Transfers/sec：Disk每秒執行讀寫操作的次數
• Disk Reads Bytes/sec ：在Disk執行讀操作時，每秒從Disk讀取的位元組數量
• Disk Bytes/sec：當Disk執行讀寫操作時，每秒從Disk讀取到記憶體的，或從記憶體寫入到Disk的位元組數量，好的Disk，其值在20-40MB之間，差的Disk，其值在20MB以下。
• Avg. Disk Queue Length ：提供Disk阻塞程度的主要度量值，表示在 sample interval期間，Disk等待處理的IO請求隊列的平均長度，即等待被Disk處理的IO請求的數量，隊列的長度要考慮到RAID，如果儲存後台使用100塊物理disk，那麼該計數值達到100，這個值是正常的，理論上，每塊物理disk的隊列長度不應該長時間大於2.
• Avg. Disk sec/Transfer：Disk每一次讀寫操作所用的平均時間
• Avg. Disk sec/Read：Disk每一次讀操作所用的平均時間 
• Avg. Disk sec/Write：Disk每一次寫操作所用的平均時間

avg.Disk sec/(Transfer,Read,Write)，能夠很好的反映Disk的IO速度，所以這三個計數值經常用來衡量Disk的IO速度:

• 很好：<10ms
• 一般：10-20ms
• 有點慢：20-50ms
• 非常慢：>50ms

二，系統實體記憶體效能計數器

SQL Server在啟動並執行過程中，會持續地向記憶體中載入大量資料，如果資料長期駐留在記憶體中，那麼SQL Server 不需要申請Disk IO請求，就能直接存取資料，快速響應使用者的請求。如果SQL Server訪問的資料不在記憶體中，將會產生一個Hard Page Fault，那麼SQL Server首先指示儲存引擎將資料頁從Disk載入到記憶體中，產生PageIOLatch等待，等到資料被載入到記憶體之後，SQL Server在記憶體中訪問資料，處理使用者請求，由於Disk 的IO速度較慢，延遲高，大量的Hard Page Fault將嚴重影響SQL Server響應使用者請求的速度，因此，常用的系統級記憶體計數器跟缺頁中斷有關：

• Memory：Page Faults/sec ：每秒發生的Page Fault的數量，Page Fault包括Hard Fault 和 Soft Fault，Hard fault表示需要從Disk中讀取資料頁，Soft fault表示需要從Physical Memory中讀取資料頁，Soft Fault不會影響效能，由於Hard Fault需要訪問Disk，會產生顯著的延遲。
• Memory：Pages Input/sec：每秒發生的Hard Fault的數量，用於計算Hard Fault的百分比： Pages Input / Page Faults = % Hard Page Faults，如果百分比經常大於40%，說明系統需要經常訪問Disk擷取資料，在一定程度上說明系統存在記憶體壓力。
• Memory：Pages/sec：每秒從Disk讀取或寫入Disk的Page數量，表示記憶體和Disk互動的Page的數量：將Page儲存到Disk或從Disk讀取資料到記憶體的Page的數量。

三，SQL Server的Buffer Manager計數器

Buffer Manager計數器用於監視SQL Server如何使用記憶體資料頁和計畫快取，讀取和寫入資料頁時的Disk IO。由於Buffer Pool是SQL Server記憶體最活躍，使用最多的部分，所以也是最容易出現效能瓶頸的部分，計數值尤其重要：

• Buffer Cache hit ration：從Buffer Pool中直接讀取，不需要從Disk中讀取的資料頁的百分比，也叫命中率，這個計數器表示，在SQL Server讀取資料時，資料存在於記憶體中，跟資料駐留在記憶體中的時間和記憶體壓力關係不大，僅供參考。
• Page Writes/sec：每秒寫入到Disk的資料頁數，和記憶體使用量關係不大，跟使用者修改的資料量有關
• CheckPoint Pages/sec：將資料重新整理到Disk的Dirty Pages的數量，和記憶體使用量關係不大，跟使用者修改的資料量有關，如果使用者對資料庫做了很多修改操作，那麼記憶體中修改過的資料髒頁就會比較多，每次重新整理的髒頁數量就會比較大
• Lazy Writes/sec：被LazyWriter重新整理的buffer數量，如果是髒頁，那麼將buffer寫入到Disk，並將buffer空間標記為Free，如果不是髒頁，那麼該buffer空間也被標記為Free，LazyWriter的作用是維護一定數量的Free buffer，SQL Server使用Free buffer來載入新的資料頁。
• Page Life Expectancy：PLE，資料頁駐留在記憶體中的時間。如果SQL Server沒有新的記憶體需求，或有閒置記憶體來完成新的記憶體需求，那麼Lazy Writer不會被處罰，Page會一直駐留在Buffer Pool中，那麼Page Life Expectancy會維持在一個比較高的水平；如果Page Life總是高高低低，表明SQL Server存在記憶體壓力。PLE的參考數值是：Max Server Memory/4GB*300s，如果PLE值長期低於參考值，記憶體可能存在瓶頸。
• Page Reads/sec：每秒從Disk讀取的資料頁數，即物理讀的次數，如果使用者訪問的資料都緩衝在記憶體中，那麼SQL Server不需要從物理Disk上讀取頁面。由於物理IO的開銷大，Page Reads操作一定會影響SQL Server的效能。
• Free list stalls/sec：等待一個Free Page的請求數量，SQL Server申請從Disk載入一個Page到記憶體中，必須在記憶體中分配一個Buffer，Buffer Manager負責維護Free Buffer List，如果Free List沒有任何Free Buffer，那麼請求必須等待，直到有閒置Buffer使用，才能將Disk中的Page載入到記憶體中。

經常使用後四種計數器，探測系統的記憶體壓力，前三種，僅供參考，在此，感謝 wy123 的協助。

四，SQL Server的Memory Manager計數器

Memory Manager計數器用於監控伺服器記憶體總體使用方式，在一個非常繁忙的系統中，Lock記憶體和授予記憶體是常用的計數器：

• Total Server Memory (KB)：SQL Server當前使用的記憶體總量
• Target Server Memory (KB)：SQL Server能夠使用的記憶體總量
• Lock Memory (KB)：SQL Server用於鎖的記憶體總量
• Grant Workspace Memory (KB)：授予記憶體，SQL Server用於執行hash，排序和建立Index操作而消耗的記憶體總量
• Memory Grants Pending (KB)：等待記憶體授與的進程數量，如果進程不能獲得指定數量的記憶體，那麼進程將不會開始執行

五，使用Performance Counter監控SQL Server資料庫系統的整體效能

建立兩個Data Set：Disk Activity，用於監控物理磁碟的活動；Memory Activity ，用於監控系統記憶體的Hard Fault和SQL Server的記憶體使用量。

 

下文摘抄自《硬碟的讀寫原理》，作者是真實的歸宿，寫的非常詳細：

訪盤請求完成過程

當需要從磁碟讀取資料時，系統會將資料邏輯地址傳給磁碟，磁碟的控制電路按照定址邏輯將邏輯地址翻譯成物理地址，即確定要讀的資料在哪個磁軌，哪個扇區。 為了讀取這個扇區的資料，需要將磁頭放到這個扇區上方，為了實現這一點，磁頭需要移動對準相應磁軌，這個過程叫做尋道，所耗費時間叫做尋道時間，然後磁碟 旋轉將目標扇區旋轉到磁頭下，這個過程耗費的時間叫做旋轉時間。

即一次訪盤請求（讀/寫）完成過程由三個動作組成：

• 尋道（時間）：磁頭移動定位到指定磁軌
• 旋轉延遲（時間）：等待指定扇區從磁頭下旋轉經過
• 資料轉送（時間）：資料在磁碟與記憶體之間的實際傳輸

因此在磁碟上讀取扇區資料（一塊資料）所需時間：Ti/o=尋道時間 +旋轉時間 + n *傳輸時間

磁碟的讀寫原理

系統將檔案儲存體到磁碟上時，按柱面、磁頭、扇區的方式進行，即最先是第1磁軌的第一磁頭下（也就是第1盤面的第一磁軌）的所有扇區，然後，是同一柱面的下一磁頭，……，一個柱面儲存滿後就推進到下一個柱面，直到把檔案內容全部寫入磁碟。系統也以相同的順序讀出資料。讀出資料時通過告訴磁碟控制卡要讀出扇區所在的柱面號、磁頭號和扇區號（物理地址的三個組成部分）進行。磁碟控制卡則直接使磁頭組件移動到相應的柱面，選通相應的磁頭，等待要求的扇區移動到磁頭下。在扇區到來時，磁碟控制卡對扇區進行讀寫操作。

局部性原理與磁碟預讀

由於儲存介質的特性，磁碟本身存取就比主存慢很多，再加上機械運動耗費，磁碟的存取速度往往是主存的幾百分分之一，因此為了提高效率，要盡量減少磁碟I/O。為了達到這個目的，磁碟往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個位元組，磁碟也會從這個位置開始，順序向後讀取一定長度的資料放入記憶體。這樣做的理論依據是電腦科學中著名的局部性原理：

• 當一個資料被用到時，其附近的資料也通常會馬上被使用。
• 程式運行期間所需要的資料通常比較集中。
• 由於磁碟順序讀取的效率很高（不需要尋道時間，只需很少的旋轉時間），因此對於具有局部性的程式來說，預讀可以提高I/O效率。

預讀的長度一般為頁（page）的整倍數，頁是電腦管理儲存空間的邏輯塊，硬體及作業系統往往將主存和磁碟儲存區分割為連續的大小相等的塊，每個儲存塊稱為一頁（在許多作業系統中，頁得大小通常為4k），主存和磁碟以頁為單位交換資料。當程式要讀取的資料不在主存中時，會觸發一個缺頁異常，此時系統會向磁碟發出讀盤訊號，磁碟會找到資料的起始位置並向後連續讀取一頁或幾頁載入記憶體中，然後異常返回，程式繼續運行。

 

拓展閱讀：常用的系統記憶體效能計數器的描述

Page Faults/sec is the average number of pages faulted per second. It is measured in number of pages faulted per second because only one page is faulted in each fault operation, hence this is also equal to the number of page fault operations. This counter includes both hard faults (those that require disk access) and soft faults (where the faulted page is found elsewhere in physical memory.) Most processors can handle large numbers of soft faults without significant consequence. However, hard faults, which require disk access, can cause significant delays.

Page Reads/sec is the rate at which the disk was read to resolve hard page faults. It shows the number of reads operations, without regard to the number of pages retrieved in each operation. Hard page faults occur when a process references a page in virtual memory that is not in working set or elsewhere in physical memory, and must be retrieved from disk. This counter is a primary indicator of the kinds of faults that cause system-wide delays. It includes read operations to satisfy faults in the file system cache (usually requested by applications) and in non-cached mapped memory files. Compare the value of Memory\\Pages Reads/sec to the value of Memory\\Pages Input/sec to determine the average number of pages read during each operation.

Pages Input/sec is the rate at which pages are read from disk to resolve hard page faults. Hard page faults occur when a process refers to a page in virtual memory that is not in its working set or elsewhere in physical memory, and must be retrieved from disk. When a page is faulted, the system tries to read multiple contiguous pages into memory to maximize the benefit of the read operation. Compare the value of Memory\\Pages Input/sec to the value of  Memory\\Page Reads/sec to determine the average number of pages read into memory during each read operation.

Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. This counter is a primary indicator of the kinds of faults that cause system-wide delays.  It is the sum of Memory\\Pages Input/sec and Memory\\Pages Output/sec.  It is counted in numbers of pages, so it can be compared to other counts of pages, such as Memory\\Page Faults/sec, without conversion. It includes pages retrieved to satisfy faults in the file system cache (usually requested by applications) non-cached mapped memory files.

 

參考文檔：

Measuring Disk Latency with Windows Performance Monitor (Perfmon)

SQL Server disk performance metrics – Part 1 – the most important disk performance metrics

Performance Monitor Counters

硬碟的讀寫原理

Great SQL Server Debates: Buffer Cache Hit Ratio

【轉】SQLSERVER磁碟原理