[SQL SERVER][Performance]找出硬體Bottleneck
回想六年前剛接觸SQL Server2000，當時公司沒有DBA這種角色，大多是開發人員同時也是DBA，但在這種球員兼裁判的模式下對於DB的管理幾乎都不重視，可是當DB出現問題時總要有人背黑鍋(小弟太菜了~XD)，當時我對sql2000可說模不著頭緒，因為sql2000相關運作猶如黑箱作業，你沒辦法獲取太多有效資訊來驗證自己的假設是否正確(這也代表者無法說服別人)，外加那時公司以產出為原則，根本沒有人想好好管理Sql Server，雖然當時自己有花時間去學習相關知識(反而引起我踏入Oracle DB領域)，但上頭較注重是你的開發進度，所以我當時也練了一身好武功來對付Sql Server，只要user反應無法連線、記憶體不足或查詢變慢...等疑難雜症，二話不說必殺技使出來~"重開機"~~哈哈(重開才是王道~~還好只是user side可以這樣搞)。
由於前些日子因為公司客戶反應軟體執行效率以達到讓她們無法忍受希望可以改善，當時主管對我說有個DB performance issue搞一下吧(心想那有啥問題，平常每天不都在搞Oracle~爽快答應)，那SQL2005幫忙看看摟(他X的!怎麼是SQL Server，哀!只有來首小黑唱的:我不摸SQL好多年.....)，不過竟然答應了就得拿出積極態度來處理，廢話不多說(也抱怨完了~哈)切入主題。
文章均為自己見解，如有錯誤還請指教
這篇記錄我自己在找出硬體Boottlenek的想法和過程，並非整體SQL Server Performance Tuning。因為SQL performance tuning個人認為還需有相當經驗當後盾、對SQL Server熟悉程度及DB架構設計和Windows記憶體架構有一定的了解程度(小弟都還太嫩)，當然興趣、廣泛吸取知識和有合作人一同討論也很重要，才能真正掌握住對的方向，提出正確建議，免得到頭來白忙一場。
當你確定效能瓶頸來自硬體時，如RAM、CPU或Hard Disk要求客戶花錢購買時，一定要提出相關數據來證明自己的假設是正確的，那麼數據該如何取得呢??這時我們可以利用performance counter來監控相關資源的使用情況以利協助分析。面對SQL Server檢查硬體順序:RAM、CPU、Hard Disk。
依環境產生自己的項目清單:
硬體環境項目:熟悉硬體環境方便你可以快速切入及判斷硬體瓶頸發生在那個環節上(孫子有說:知彼知己百戰百勝)，大致上我分三類OS、HardWare及DataBase
| 類別 | 細項 |
| OS | 版本 |
| 虛擬記憶體 | |
| 硬碟有無啟用文件壓縮,加密 | |
| 是否為獨立的SQLSERVER | |
| 是否設定背景服務 | |
| 是否有安裝防毒軟體 | |
| 是否停用不必要網路服務 | |
| HARDWARE |
CPU: 是否開啟Hyper Threading L2 Cache Size CPU等級及時脈 |
|
實體記憶體: total size 記憶體等級及時脈 |
|
|
DISK: Number of disks RAID Level 硬碟種類 硬碟轉速和緩衝區大小 |
|
| NIC:網卡等級及吞吐量 | |
| DataBase | 版本類型 |
|
參數及屬性設定: Min Server Memory Max Server Memory affinity mask awe enabled cost threshold for parallelism cursor threshold fill factor (%) index create memory (KB) lightweight pooling max degree of parallelism min memory per query (KB) network packet size (B) priority boost query governor cost limit query wait (s) recovery interval (min) set working set size user connections 定序設定 遠端查詢逾時設定(s) 自動更新統計值 自動非同步更新統計資料 自動建立統計資料 自動壓縮 |
硬體效能計數器清單:列出個人認為比較重要及具有參考價值得計數器
CPU
| performance name | 建議值 | 說明 |
| Processor: % Processor Time | <80% | 紀錄每個CPU的利用率,如果值持續超過80%,就要考慮增加CPU數量或更換較大的L2 cache |
| Processor: %Privileged Time |
< 30% of Total %Processor Time |
此進程所分配的無法與其它進程共享的當前字節數量。如果系統性能隨著時間而降低，則此計數器可以是memory leak的最佳指示器。 |
| System: Processor Queue Length |
<2 |
相當於等候處理器空檔時間的執行緒數目。4顆CPU該值不應該超過8,如果超過就要考慮增加CPU數量或更換較大的L2 cache |
|
System:Context Switches/sec |
<5000 |
該值如果超過5000值有可能是CPU瓶頸,但也有可能是記憶體不足造成頻繁換頁導致CPU繁忙 |
|
Process:Page Faults/sec |
<400 |
將進程產生的頁故障與系統產生的相比較，以判斷這個進程對系統頁故障產生的影響。 |
RAM
| performance name | 建議值 | 說明 |
|
Memory: Pages/sec |
0~20/sec |
記錄每秒鐘記憶體和硬碟之間的交換頁面，頁面交換頻繁超過SERVER所能承受IO，將降低性能,所以要盡可能壓低值,如果一直超過就要考慮加記憶體 |
|
Memory: Available MBytes |
>200MB |
紀錄SQLSERVER可用的實體記憶體,長時間如果維持低於200Mb(留給OS代表電腦整體地缺乏記憶體，或有某個應用程式沒有釋出記憶體,就要考慮加記憶體或解決應用程式 |
|
Memory: Page Faults/sec |
<80 |
如果大於80表示過度硬體分頁錯誤將考慮增加記憶體或是緩衝區設定過大 |
|
Memory: Committed Bytes |
可以查看確認memory leak行為,評估 PLE 和 Lazy Writes/sec 的檢查點。 |
|
|
Memory: Committed Limit |
<實體記憶體 |
如果長時間大於實體記憶體就要考慮增加實體記憶體 |
|
SQL Server:Buffer Manager Buffer Cache Hit Ratio |
91%~99% |
紀錄獲取資料的頻率是從記憶體而不是從硬碟,如果該值持續不在範圍內就要考慮增加記憶體 |
|
SQL Server:Buffer Manager Lazy Writes/Sec |
<20 |
一般來說該值不會很高,0~20是可接受範圍(越接近0表示SQL的緩衝區很足夠且沒有dirty pages),如果持續超過20將考慮增加記憶體 |
|
SQL Server:Buffer Manager Free Pages |
> 640 |
微軟建議不可低於640,如太低可調高max memory或增加記憶體 |
|
SQL Server:Buffer Manager Free list stalls/sec |
< 2 |
顯示資料庫可用頁數請求頻率(因為緩衝區不足),該直如果持續大於2代表記憶體不足 |
|
SQL Server:Buffer Manager Page Life Expectancy |
>300 |
記憶體指標之一,如果該值持續<300那麼將考慮增加記憶體 |
|
SQL Server:Buffer Manager Checkpoints Pages/sec |
當下列情況發生時，代表 Memory presure |
|
|
SQLServer:Memory Manager Target ServerMemory(KB) |
<實體記憶體
|
SQL Server可使用的記憶體總數 若 Target ServerMemory (KB) 計數器和電腦中的實體記憶體數量相比一直很高，可能代表需要更多的記憶體。 |
|
SQLServer:Memory Manager Total ServerMemory(KB) |
< SQLServer:Memory Manager Target ServerMemory(KB) |
SQL Server正使用的記憶體總數(來自緩衝區) 若 Total Server Memory (KB) 計數器和電腦中的實體記憶體數量相比一直很高，可能代表需要更多的記憶體。也就有了資源競爭(Contention)問題,因作業系統較難配置記憶體來執行一般性作業 |
|
Process:Working Set |
如果這個數字一直低於「最小伺服器記憶體」與「最大伺服器記憶體」伺服器選項設定的記憶體數量，則代表 SQL Server 設定的記憶體過多。 |
Disk
| performance name | 建議值 | 說明 |
| Physical Disk: % Disk time |
<70% |
紀錄磁碟陣列繁忙程度,原則上要低於70%,如果持續超過70%(24小時監控周期大約超過10分鐘),就要考慮更換轉速更快硬碟 |
|
Physical Disk: Avg. Disk Queue Length |
<2 |
平均有多少系統要求,正在等取存後。 |
|
Physical Disk: Current Disk Queue Length |
<2 |
|
|
Physical Disk: Disk Reads/sec |
最佳:<15ms |
一般而言，Ultra Wide SCSI 磁碟每秒可以處理 50 至 70 次 的 I/O 操作 |
|
Physical Disk: Disk Writes/sec |
可參考每家硬碟廠商所提供數據 | |
|
LogicalDisk:% Free Space |
指出logicalDisk空間不足,所以當logicalDisk處存的是交易紀錄檔時,該指標資訊很重要 |
|
|
SQL Server:Databases Data File(s) Size(KB) |
資料庫內所有資料檔總計大小包含任何自動成長 |
|
|
SQL Server:Databases Percent Log Used |
使用中的紀錄檔空間百分比 |
|
|
SQL Server:Log Growths Log Growths |
資料庫交易紀錄檔的擴大總次數 |
有了以上的清單，就可以按表操課取得數據，當然如果可以模擬改善後的數據，如加RAM前查詢時間及加RAM後時間比較，硬碟及CPU..等，相信會讓客戶更願意掏錢購買硬體(而非只要遇到效能問題就只能花錢買硬體改善，有時花錢還買不到效果那就慘了)。
PS:
往往遇到效能上的問題，大多沒有經過壓力測試(所以也沒有任何硬體和DB相關數據基準線)或開發時(TSQL語法及SP用法..等)效能就沒注意，交付時間一到產品雖可如期上線，一開始或許都沒麻煩(資料量小)，但半年或一年後(資料成長速度超乎預期)卻得應付往後的效能災難，到時就得須花更多人力及財力得不償失。