Sql Server CPU 性能排查及优化
时间：2021-07-01 10:21:17
帮助过：15人阅读
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
(select top 50 qs.
*
from sys.dm_exec_query_stats qs
order by qs.total_worker_time
desc)
as c
cross apply sys.dm_exec_sql_text(plan_handle)
as q
order by c.total_worker_time
desc
go
-- 返回最经常运行的100条语句
SELECT TOP 100 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,
(case when qs.statement_end_offset
= -1
then len(
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
inner join 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
ORDER BY [dbid],
[Usecounts] DESC
-- 返回做IO数目最多的50条语句以及它们的执行计划
select top 50
(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 <=0 then 64000
else (statement_end_offset
-statement_start_offset)
/2 end)
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
order by
(total_logical_reads + total_logical_writes)
/Execution_count
Desc
-- 计算signal wait占整wait时间的百分比
-- 指令等待 CPU 资源的时间占总时间的百分比。如果超过 25% ，说明 CPU 紧张
select convert(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 @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)
-- 查询当前数据库上所有用户表格在Row lock上发生阻塞的频率
declare @dbid int
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
where objectproperty(s.
object_id,
‘IsUserTable‘)
= 1
and i.
object_id = s.
object_id
and i.index_id
= s.index_id
order by row_lock_wait_count
desc
--End Cpu 分析优化的相关 Sql
Sql Server CPU 性能排查及优化
标签：.sql bsp logical str style bst cas wait tab
