資料庫效能調教 — CPU Pressure
如何透過 DMV (Dynamic Management View)了解 CPU 效能瓶頸?
SQL Server 對於歷史所查詢過或是執行過的指令，
SQL Server 都會將該相關的統計資訊儲存於 DMV
這次要介紹的 DMV 為 “sys.dm_os_wait_stats”
該 DMV (Dynamic Management View)
儲存自上次 SQL Server service 啟動以來所有累積的歷史資訊
換句話說，當SQL Server reboot or restart services 時，該資訊就會被清空
另外，也可以用下列指令來清空資訊內容
DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR);
特別是當 index 重建或是新增的時候，
我們可以清空該統計資訊內容來重新了解 Index 查詢的效率
CPU Pressure
DMV (Dynamic Management View)可以透過 Signal wait time 來知道CPU 是否為效能頻景.
讓我們先介紹什麼是 signal wait time ?
Total Response (wait_time_ms) = Signal Wait time (signal_wait_time_ms) + Resource Wait time
Signal Wait time 為等待 CPU 執行的時間，由 runnable 狀態等待進入 running
因此，如果 signal wait time 很長的話，也表示 CPU 的效能瓶頸
可以用 Signal Wait time / Total Response time 的比例來得知
是否 signal wait time 佔wait time 很大的比例，Query 如下：
由於該 DMV 是”累積”性的資訊，因此可以每隔一段時間就觀察一次
每隔一段時間 reset 一次，再進行觀察
DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR);
另外，針對 CPU 相關的 performance counters
- Processor/ %Privileged Time:
表示 CPU 花在處理 windows kernel 的時間，最常見的，例如 Disk I/O，有可能為大量的 page in/out 或是 Table Scan 造成的 Disk I/O 或是 transaction log 的寫入與讀取等。
- Process (sqlservr.exe)/ %Processor Time
SQL Server process 所花的 CPU 時間
當確認為 CPU 效能瓶頸時，
接著可以運用 Query 相關的 DMV 將造成該 CPU 瓶頸的 query 找出來，
其中會用到的 DMV 有：
- sys.dm_exec_query_stats : Query 相關的統計資訊
- sys.dm_exec_sql_text : Query 的指令，例如 SELECT…….
- sys.dm_exec_query_plan: Query Execution plan
總結
1. 這篇文章主要說明如何運用 DMV “sys.dm_os_wait_stats” 來找出潛在的 CPU 效能瓶頸
CPU 的效能瓶頸取決於 Signal Wait (由 Runnable 狀態等待進入 Running)
Total Response = Signal Wait + Resource Wait
因此我們可以藉由 Signal Wait / Total Response 的比例
定期來觀察是否Total Response time 中，Signal Wait 的比例
如果 Signal Wait 很大，表示有潛在的 CPU 效能瓶頸
2. 因此，進一步透過 performance counters 輔助，提供額外的資訊
- Processor/ %Privileged Time
- Process (sqlservr.exe)/ %Processor Time
3. 確認 CPU 效能瓶頸時，我們進一步找出造成該瓶頸的
SQL 指令 (sys.dm_exec_sql_text)
SQL Execution plan (sys.dm_exec_query_plan)
SQL 執行的統計資訊 (sys.dm_exec_query_stats)
同樣的方法與流程，也可以找出其他資源的效能瓶頸