22,301
社区成员




--单次执行耗费CPU时间最长的查询计划
SELECT TOP 5 total_worker_time,last_worker_time,max_worker_time,min_worker_time,
SUBSTRING(st.text,(qs.statement_start_offset/2)+1,
((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
-qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY max_worker_time DESC
不懂,帮顶,学习,蹭分.
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
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 N'%<SystemHealth>%') AS x
) AS y
ORDER BY record_id DESC;
Oracle使用V$OSSTAT视图 查询cpu使用率
OS统计数据
在仔细检查用户的性能问题时,John还需要排除主机系统是瓶颈的可能性。在采用Oracle 10g以前,
他可以使用操作系统(OS)工具,如sar 和vmstat,并推断出一些确定争用问题的度量指标。在Oracle
10g中,在数据库中自动采集OS级别的度量指标。为了查看潜在的主机争用问题,John对V$OSSTAT视图执
行下面的查询:
select * from v$osstat;
代码清单6给出的输出结果显示了所采集的OS级别的各种度量指标元素。所有时间元素都以厘秒为单
位。从代码清单6显示的结果中John了解到,系统的一个CPU有51025805厘秒空闲(IDLE_TICKS)、
2389857厘秒繁忙(BUSY_TICKS),这表明CPU有大约4%的时间繁忙。
2389857/(51025805 +2389857) = 4%
从中他得出结论,在主机中CPU不是瓶颈。请注意,如果主机系统有多于1个的CPU,则标头中有AVG_前缀的
列,如AVG_IDLE_TICKS将显示所有CPU的这些度量指标的平均值。