SQL Server 性能排查及优化相关SQL

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 q
orderby c.total_worker_time desc
go
-- 返回最经常运行的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 statement
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
innerjoin sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
and cp.usecounts>4
ORDERBY[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),
case
when (statement_end_offset -statement_start_offset)/2<=0then64000
else (statement_end_offset -statement_start_offset)/2end) as exec_statement, sql_text.text,plan_text.*
from sys.dm_exec_query_stats 
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
cross apply sys.dm_exec_query_plan(plan_handle) as plan_text
orderby
(total_logical_reads + total_logical_writes) /Execution_count Desc
-- 计算signal wait占整wait时间的百分比
-- 指令等待 CPU 资源的时间占总时间的百分比。如果超过 25% ，说明 CPU 紧张
selectconvert(numeric(5,4),sum(signal_wait_time_ms)/sum(wait_time_ms))
from Sys.dm_os_wait_stats
-- 计算'Cxpacket'占整wait时间的百分比
-- Cxpacket:Sql Server 在处理一句代价很大的语句，要不就是没有合适的索引或筛选条件没能筛选足够的记录，使得语句要返回大量的结果，当 >5% 说明有问题
declare@Cxpacketbigint
declare@Sumwaitsbigint
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
selectconvert(numeric(5,4),@Cxpacket/@Sumwaits)
-- 查询当前数据库上所有用户表格在Row lock上发生阻塞的频率
declare@dbidint
select@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 i
whereobjectproperty(s.object_id,'IsUserTable') =1
and i.object_id= s.object_id
and i.index_id = s.index_id
orderby row_lock_wait_count desc
--End Cpu 分析优化的相关 Sql