DMV {dm_os_ring_buffers} - Queries to help pinpoint current Issues / usual usage patterns
I'm been running some queries (below) to help me identify when I have had time-sensitive performance issues around Memory/CPU, I didn't want to load up additional overhead to the system (unless absolutely neccessary) using traces or profiler - naturally we have various methods to do this Perfmon counters, DBCC, DMVs etc..
One quick way I like is to run a few DMV queries (normally back in seconds) to help me find those RECENT specific time periods when the system has been substantially changed in some way using, this is using the DMV dm_os_ring_buffers
This one helps me identify when I'm expericing Timeout Errors (1222).. modiy code to look for other error as highlight below
DECLARE @ts_now BIGINT,@dt_max BIGINT, @dt_min BIGINT SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info SELECT @dt_max = MAX(timestamp), @dt_min = MIN(timestamp) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION' SELECT record_id ,DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime ,y.Error ,UserDefined ,b.description as NormalizedText FROM ( SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, record.value('(./Record/Exception/Error)[1]', 'int') AS Error, record.value('(./Record/Exception/UserDefined)[1]', 'int') AS UserDefined, TIMESTAMP FROM ( SELECT TIMESTAMP, CONVERT(XML, record) AS record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION' AND record LIKE '% %' ) AS x ) AS y INNER JOIN sys.sysmessages b on y.Error = b.error WHERE b.msglangid = 1033 and y.Error = 1222 ORDER BY record_id DESC
Sample Output
| record_id |
EventTime |
Error |
UserDefined |
NormalizedText |
| 15199195 |
18/03/2010 14:00 |
1222 |
0 |
Lock request time out period exceeded. |
| 15199194 |
18/03/2010 14:00 |
1222 |
0 |
Lock request time out period exceeded. |
| 15199193 |
18/03/2010 14:00 |
1222 |
0 |
Lock request time out period exceeded. |
| 15199192 |
18/03/2010 14:00 |
1222 |
0 |
Lock request time out period exceeded. |
| 15199191 |
18/03/2010 14:00 |
1222 |
0 |
Lock request time out period exceeded. |
This one helps me identify when I have Unusally High Processing (> 50%) or # Page-Faults
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,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/UserModeTime)[1]', 'bigint') AS UserModeTime,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime)[1]', 'bigint') AS KernelModeTime,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/PageFaults)[1]', 'bigint') AS PageFaults,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta)[1]', 'bigint') AS WorkingSetDelta,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]', 'int') AS MemoryUtilization,TIMESTAMPFROM ( 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 '% %'
) AS x
Example: Showing entries > 50% SQL CPU
| record_id |
SystemIdle |
SQLProcessUtilization |
UserModeTime |
KernelModeTime |
PageFaults |
WorkingSetDelta |
MemoryUtilization |
TIMESTAMP |
| 111916 |
66 |
29 |
36718750 |
1374843750 |
21333 |
-40960 |
100 |
7991061289 |
| 111917 |
54 |
41 |
50156250 |
1954062500 |
26914 |
-28672 |
100 |
7991121290 |
| 111918 |
57 |
39 |
42968750 |
1838437500 |
30096 |
20480 |
100 |
7991181290 |
| 111919 |
41 |
53 |
43906250 |
2530156250 |
22088 |
-4096 |
100 |
7991241307 |
| 111920 |
48 |
45 |
40937500 |
2124062500 |
26395 |
8192 |
100 |
7991301310 |
| 111921 |
52 |
43 |
35625000 |
2052812500 |
21996 |
155648 |
100 |
7991361311 |
| 111922 |
40 |
55 |
36875000 |
2637343750 |
33355 |
-262144 |
100 |
7991421311 |
| 111923 |
36 |
58 |
44843750 |
2786562500 |
47019 |
28672 |
100 |
7991481311 |
| 111924 |
31 |
64 |
53437500 |
3046562500 |
31027 |
61440 |
100 |
7991541314 |
| 111925 |
36 |
57 |
43906250 |
2711250000 |
37074 |
-8192 |
100 |
7991601317 |
| 111926 |
52 |
43 |
43437500 |
2060156250 |
29176 |
20480 |
100 |
7991661318 |
| 111927 |
71 |
24 |
33750000 |
1141250000 |
14478 |
16384 |
100 |
7991721320 |
| 111928 |
71 |
23 |
34531250 |
1116250000 |
12711 |
-20480 |
100 |
7991781320 |
| 111929 |
53 |
36 |
46562500 |
1714062500 |
26684 |
200704 |
100 |
7991841323 |
Finally one to provide some understanding of the level of memory state changes that are ocuring
SELECT record.value('(./Record/@id)[1]', 'int') AS 'record_id',record.value('(./Record/ResourceMonitor/Notification)[1]', 'VARCHAR(100)') AS 'ReservedMemory',record.value('(./Record/ResourceMonitor/Indicators)[1]', 'int') AS 'Indicators',record.value('(./Record/ResourceMonitor/Effect/@state)[1]', 'VARCHAR(100)') + ' - ' + record.value('(./Record/ResourceMonitor/Effect/@reversed)[1]', 'VARCHAR(100)') + ' - ' + record.value('(./Record/ResourceMonitor/Effect)[1]', 'VARCHAR(100)') AS 'APPLY-HIGHPM',record.value('(./Record/ResourceMonitor/Effect/@state)[2]', 'VARCHAR(100)') + ' - ' + record.value('(./Record/ResourceMonitor/Effect/@reversed)[2]', 'VARCHAR(100)') + ' - ' + record.value('(./Record/ResourceMonitor/Effect)[2]', 'VARCHAR(100)') AS 'APPLY-HIGHPM',record.value('(./Record/ResourceMonitor/Effect/@state)[3]', 'VARCHAR(100)') + ' - ' + record.value('(./Record/ResourceMonitor/Effect/@reversed)[3]', 'VARCHAR(100)') + ' - ' + record.value('(./Record/ResourceMonitor/Effect)[3]', 'VARCHAR(100)') AS 'REVERT_HIGHPM',record.value('(./Record/MemoryNode/ReservedMemory)[1]', 'int') AS 'ReservedMemory',record.value('(./Record/MemoryNode/CommittedMemory)[1]', 'int') AS 'CommittedMemory',record.value('(./Record/MemoryNode/SharedMemory)[1]', 'int') AS 'SharedMemory',record.value('(./Record/MemoryNode/AWEMemory)[1]', 'int') AS 'AWEMemory',record.value('(./Record/MemoryNode/SinglePagesMemory)[1]', 'int') AS 'SinglePagesMemory',record.value('(./Record/MemoryNode/CachedMemory)[1]', 'int') AS 'CachedMemory',record.value('(./Record/MemoryRecord/MemoryUtilization)[1]', 'int') AS 'MemoryUtilization',record.value('(./Record/MemoryRecord/TotalPhysicalMemory)[1]', 'int') AS 'TotalPhysicalMemory',record.value('(./Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'int') AS 'AvailablePhysicalMemory',record.value('(./Record/MemoryRecord/TotalPageFile)[1]', 'int') AS 'TotalPageFile',record.value('(./Record/MemoryRecord/AvailablePageFile)[1]', 'int') AS 'AvailablePageFile',record.value('(./Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS 'TotalVirtualAddressSpace',record.value('(./Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS 'AvailableVirtualAddressSpace',record.value('(./Record/MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]', 'bigint') AS 'AvailableExtendedVirtualAddressSpace',
TIMESTAMP
FROM ( SELECT TIMESTAMP, CONVERT(XML, record) AS record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR' AND record LIKE '% %' ) AS x