六、CPU優化（6）DMV與計數器

weixin_34219944發表於2013-08-10
優化

一、CPU相關的DMV

1. 檢視使用CPU最多的前50名

select highest_cpu_queries.*, q.dbid, q.objectid, q.number, q.encrypted, q.[text]

from

( select top 50 qs.*

from sys.dm_exec_query_stats qs

order by qs.total_worker_time desc ) as highest_cpu_queries

cross apply sys.dm_exec_sql_text(plan_handle) as q

order by highest_cpu_queries.total_worker_time desc


2. 檢視最頻繁重編譯的前20個儲存過程

select top 20 sql_text.text, sql_handle, plan_generation_num, execution_count, dbid, objectid

from sys.dm_exec_query_stats a

cross apply sys.dm_exec_sql_text(sql_handle) as sql_text

where plan_generation_num > 1

order by plan_generation_num desc


3. 計算signal wait佔整wait時間的百分比

select CONVERT(numeric(5,4),sum(signal_wait_time_ms)/sum(wait_time_ms))

from sys.dm_os_wait_stats

該值描述了指令等待CPU資源的時間佔總時間的百分比。如果超過25%，說明CPU資源緊張。


4. 並行執行的Cxpacket等待狀態

declare @Cxpacket bigint

declare @Sumwaits bigint

select @Cxpacket = wait_time_ms from sys.dm_os_wait_stats where wait_type = 'Cxpacket'

select @Sumwaits = SUM(wait_time_ms) from sys.dm_os_wait_stats

select CONVERT(numeric(5,4),@Cxpacket/@Sumwaits)

　　並行執行意味著SQL Server查詢引擎估算某一句語句執行的代價將會超過成本代價，或者沒有合適的索引，或者篩選條件沒能夠篩選掉足夠的記錄，使得語句要返回大量的結果。為了提升OLTP系統的效能，這些都是儘量避免的。



二、CPU相關的效能計數器

1. 檢查整個伺服器的CPU使用情況

Processor: % Processor Time

Processor: % Privileged Time (Kernel Mode)

Processor: % User Time (User Mode)

System: Processor Queue length

Context switches/sec



2. 檢查每個程式的CPU使用情況

Processor: % Processor Time

Processor: % Privileged Time

Processor: % User Time



3. 估算執行計劃重用率

　　關注SQLServer:SQL Statistics的計數器，大致估算出執行計劃重用率。對於OLTP系統的核心語句，必須有大於95%的執行計劃重用率。

　　Initial Compilations = SQL Compilations/sec - SQL Re-Comilations/sec

　　執行計劃重用率 = （Batch requests/sec - Initial Compilations/sec) / Batch requests/sec



本文結語：

　　通過監視計數器，或者使用DMV，檢視CPU的效能。

相關文章