六、CPU优化（6）DMV与计数器

一、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的性能。