query the list of factor on performance impact

weixin_30455023

于 2009-05-03 14:06:00 发布

阅读量49

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/yanbinboy/archive/2009/05/03/1448116.html

本文介绍了一种SQL Server等待统计的分析方法，通过查询系统管理视图dm_os_wait_stats来获取等待类型、等待时间等信息，并按等待时间降序排列。此外，还展示了如何计算每个等待类型的百分比以及累积百分比。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

with waits as
(
select wait_type,
wait_time_ms / 1000 as wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) over() as pct,
ROW_NUMBER() over(order by wait_time_ms desc) as rn
from sys.dm_os_wait_stats
where wait_type not like '%sleep%'
)
select w1.wait_type,
CAST(w1.wait_time_s as decimal(12,2)) as wait_time_s,
cast(w1.pct as decimal(12,2)) as pct,-- w2.pct,w1.rn, w2.rn
cast(sum(w2.pct) as decimal(12,2)) as running_pct
from waits w1 join waits w2
on w1.rn <= w2.rn
group by w1.rn, w1.wait_type, w1.wait_time_s, w1.pct
having SUM(w2.pct) - w1.pct < 90
order by w1.rn

转载于:https://www.cnblogs.com/yanbinboy/archive/2009/05/03/1448116.html