环形缓冲区存储了有关CPU利用率的信息。这些信息每分钟更新一次。所以你可以跟踪到4小时15分钟内给定时间点的CPU利用率。下面的输出显示了SQL实例的CPU利用率和其他活动进程的CPU利用率。这将帮助我们分析是否SQL Server进程占用大量CPU。
对于SQL Server 2005:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
declare
@ts_now
bigint
select
@ts_now = cpu_ticks /
convert
(
float
, cpu_ticks_in_ms)
from
sys.dm_os_sys_info
select
record_id,
dateadd(ms, -1 * (@ts_now - [
timestamp
]), GetDate())
as
EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization
as
OtherProcessUtilization
from
(
select
record.value(
'(./Record/@id)[1]'
,
'int'
)
as
record_id,
record.value(
'(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]'
,
'int'
)
as
SystemIdle,
record.value(
'(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]'
,
'int'
)
as
SQLProcessUtilization,
timestamp
from
(
select
timestamp
,
convert
(xml, record)
as
record
from
sys.dm_os_ring_buffers
where
ring_buffer_type = N
'RING_BUFFER_SCHEDULER_MONITOR'
and
record
like
'%<SystemHealth>%'
)
as
x
)
as
y
order
by
record_id
desc
|
对于SQL Server 2008:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
declare
@ts_now
bigint
select
@ts_now = ms_ticks
from
sys.dm_os_sys_info
select
record_id, dateadd (ms, (y.[
timestamp
] -@ts_now), GETDATE())
as
EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization
as
OtherProcessUtilization
from
(
select
record.value(
'(./Record/@id)[1]'
,
'int'
)
as
record_id,
record.value(
'(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]'
,
'int'
)
as
SystemIdle,
record.value(
'(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]'
,
'int'
)
as
SQLProcessUtilization,
timestamp
from
(
select
timestamp
,
convert
(xml, record)
as
record
from
sys.dm_os_ring_buffers
where
ring_buffer_type = N
'RING_BUFFER_SCHEDULER_MONITOR'
and
record
like
'%<SystemHealth>%'
)
as
x
)
as
y
order
by
record_id
desc
|
常用SQL脚本:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
select
record
,
CAST
(record
AS
XML)
,
CAST
(record
as
XML).value(
'/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/MemoryUtilization[1]'
,
'int'
)
as
MemUtil
,
CAST
(record
as
XML).value(
'/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/PageFaults[1]'
,
'int'
)
as
PageFaults
,
CAST
(record
as
XML).value(
'/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/WorkingSetDelta[1]'
,
'int'
)
as
workingSetDelta
,
CAST
(record
as
XML).value(
'/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/KernelModeTime[1]'
,
'int'
)
as
KernelModeTime
,
CAST
(record
as
XML).value(
'/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/UserModeTime[1]'
,
'bigint'
)
as
UserModeTime
,
CAST
(record
as
XML).value(
'/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/ProcessUtilization[1]'
,
'int'
)
as
ProcessUtilization
,
CAST
(record
as
XML).value(
'/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/SystemIdle[1]'
,
'int'
)
as
SystemIdle
from
sys.dm_os_ring_buffers
where
ring_buffer_type =
'RING_BUFFER_SCHEDULER_MONITOR'
|
本文转自UltraSQL51CTO博客,原文链接:http://blog.51cto.com/ultrasql/1583973 ,如需转载请自行联系原作者