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 belowDECLARE @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-FaultsSELECT 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 ocuringSELECT 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',
TIMESTAMPFROM ( 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
