SQL Server 2005 Ring Buffers stored information related to CPU usage. This information is updated every 1 minute. So at any given point of time you can trace back the CPU usage for 4 hours 15 minutes. A sample output below shows the SQL instance CPU utilization against the other processes (active on the server) CPU utilization. This will definitely help us identify whether the SQL Server process is actually driving CPU.
Sample Output:
Event Time | SQL Process Utilization | System Idle | Other Process Utilization |
2009-11-19 20:00:49.947 | 0 | 96 | 4 |
2009-11-19 19:59:49.933 | 0 | 92 | 8 |
2009-11-19 19:58:49.910 | 0 | 91 | 9 |
2009-11-19 19:57:49.893 | 0 | 92 | 8 |
For SQL Server 2005:
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
For SQL Server 2008:
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
Pingback: T-SQL Tuesday #22–Data presentation…What’s that? « TroubleshootingSQL
Amit, FYI. We encountered a stack dump using this code, which caused about 30 minutes of downtime on a critical SQL instance. It’s a known bug with XQuery. Here’s the KB article that we were directed to for our SevA PSS case: http://support.microsoft.com/kb/2587929
LikeLike
Thanks for the post back.
LikeLike
Yes. This is a known issue with an AV dumps.
LikeLike
Pingback: SQL Server Resource Governor Monitoring reports - SQL Server Premier Field Engineer Blog - Site Home - MSDN Blogs
Pingback: How to find out the CPU usage information for the SQL Server process using Ring Buffers | TroubleshootingSQL | Sladescross's Blog