SQL Server CPU 性能排查及优化的相关SQL语句，非常好、非常实用的SQL语句:
--Begin Cpu 分析优化的相关 Sql--使用DMV来分析SQL Server启动以来累计使用CPU资源最多的语句。例如下面的语句就可以列出前50名。select c.last_execution_time,c.execution_count,c.total_logical_reads,c.total_logical_writes,c.total_elapsed_time,c.last_elapsed_time, q.[text]from (selecttop50 qs.* from sys.dm_exec_query_stats qs orderby qs.total_worker_time desc) as c cross apply sys.dm_exec_sql_text(plan_handle) as qorderby c.total_worker_time descgo-- 返回最经常运行的100条语句SELECTTOP100 cp.cacheobjtype,cp.usecounts,cp.size_in_bytes,qs.statement_start_offset,qs.statement_end_offset,qt.dbid ,qt.objectid ,SUBSTRING(qt.text,qs.statement_start_offset/2, (casewhen qs.statement_end_offset =-1 thenlen(convert(nvarchar(max), qt.text)) *2 else qs.statement_end_offset end-qs.statement_start_offset)/2) as statementFROM sys.dm_exec_query_stats qscross apply sys.dm_exec_sql_text(qs.sql_handle) as qtinnerjoin sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handlewhere cp.plan_handle=qs.plan_handleand cp.usecounts>4ORDERBY[dbid],[Usecounts]DESC-- 返回做IO数目最多的50条语句以及它们的执行计划selecttop50 (total_logical_reads/execution_count) as avg_logical_reads, (total_logical_writes/execution_count) as avg_logical_writes, (total_physical_reads/execution_count) as avg_phys_reads, Execution_count, statement_start_offset as stmt_start_offset, statement_end_offset as stmt_end_offset,substring(sql_text.text, (statement_start_offset/2),casewhen (statement_end_offset -statement_start_offset)/25% 说明有问题declare@Cxpacketbigintdeclare@Sumwaitsbigintselect@Cxpacket= wait_time_msfrom Sys.dm_os_wait_statswhere wait_type ='Cxpacket'select@Sumwaits=sum(wait_time_ms)from Sys.dm_os_wait_statsselectconvert(numeric(5,4),@Cxpacket/@Sumwaits)-- 查询当前数据库上所有用户表格在Row lock上发生阻塞的频率declare@dbidintselect@dbid=db_id()Select dbid=database_id, objectname=object_name(s.object_id), indexname=i.name, i.index_id --, partition_number, row_lock_count, row_lock_wait_count, [block %]=cast (100.0* row_lock_wait_count / (1+ row_lock_count) as numeric(15,2)), row_lock_wait_in_ms, [avg row lock waits in ms]=cast (1.0* row_lock_wait_in_ms / (1+ row_lock_wait_count) as numeric(15,2))from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s, sys.indexes iwhereobjectproperty(s.object_id,'IsUserTable') =1and i.object_id= s.object_idand i.index_id = s.index_idorderby row_lock_wait_count desc--End Cpu 分析优化的相关 Sql
