可以使用下面的语句来得到

SELECT SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,    (        (CASE qs.statement_end_offset            WHEN -1             THEN DATALENGTH(qt.TEXT)            ELSE qs.statement_end_offset            END             - qs.statement_start_offset        )/2    )+1),qs.execution_count,qs.total_logical_reads, qs.last_logical_reads,qs.total_logical_writes, qs.last_logical_writes,qs.total_worker_time,qs.last_worker_time,qs.total_elapsed_time/1000000 total_elapsed_time_in_S,qs.last_elapsed_time/1000000 last_elapsed_time_in_S,qs.last_execution_time,qp.query_planFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qtCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY qs.total_worker_time DESC -- CPU time

一些解释:

SQL Server 有很多线程, 其基本的工作逻辑为: 不让任何的runnable的thread饿死. SQL Server的线程管理并不是由操作系统管理的, 而是由SQL Server 自己管理的. SQL Server多数时候运行在nonpreemptive模式下的, 意味着线程都是互相协作的, 不时地通过主动放弃的方式来让其他的线程运行. 当一个线程为另一个线程主动出让执行权的时候, 它就创建了自己的wait状态. 如果这样的线程很多, 那么就明显地意味着存在着CPU的压力.

可以通过下面的语句来查看系统中有多少runnable的task.

SELECT scheduler_id, current_tasks_count, runnable_tasks_count, work_queue_count, pending_disk_io_countFROM sys.dm_os_schedulersWHERE scheduler_id < 255GO

如果长时间持续地发现两位数的runnable_tasks_count, 注意不是偶尔, 你就知道CPU存在压力了.

当然, 加CPU是简单的解决方案. 但是, 当这么做不可行的时候, 就可以通过文章最前面的语句来找到占用CPU资源最多的query, 进行必要的优化.

信息来源

============================

SQL SERVER – SOS_SCHEDULER_YIELD – Wait Type – Day 8 of 28

http://blog.sqlauthority.com/2011/02/08/sql-server-sos_scheduler_yield-wait-type-day-8-of-28/

我的SQL里哪个语句占用的CPU最多?