(DIR) Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
(HTM) https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
(DIR) Return to: Scripts
*****************************************************
#Post#: 118--------------------------------------------------
Ring Buffer quereis to troubleshoot performance issues
By: srinivasma_exceldbp Date: December 25, 2014, 4:04 am
---------------------------------------------------------
-- Retrieve all available ring buffers
-- System Health Analysis through sys.dm_os_ring_buffers
SELECT
ring_buffer_type,
COUNT(*) AS 'Count'
FROM sys.dm_os_ring_buffers
GROUP BY ring_buffer_type
GO
-- Retrieve data from the RING_BUFFER_RESOURCE_MONITOR ring
buffer
SELECT CAST(record AS XML), * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
GO
-- Retrieve the memory usage
SELECT
EventTime,
record.value('(/Record/ResourceMonitor/Notification)[1]',
'varchar(max)') AS 'Type',
record.value('(/Record/MemoryRecord/TotalPhysicalMemory)[1]',
'bigint') AS 'TotalPhysicalMemoryInKb',
record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)[1]'
,
'bigint') AS 'AvailablePhysicalMemoryInKb',
record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace
)[1]',
'bigint') AS 'AvailableVirtualAddressSpaceInKb',
record.value('(/Record/MemoryRecord/TotalPageFile)[1]',
'bigint') AS 'TotalPageFileInKb',
record.value('(/Record/MemoryRecord/AvailablePageFile)[1]',
'bigint') AS 'AvailablePageFile'
FROM
(
SELECT
DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks /
ms_ticks ))) - [timestamp])/1000), GETDATE()) AS EventTime,
CONVERT (xml, record) AS record
FROM sys.dm_os_ring_buffers
CROSS JOIN sys.dm_os_sys_info
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
) AS t
ORDER BY EventTime DESC
GO
-- Retrieve data from the RING_BUFFER_SCHEDULER_MONITOR ring
buffer
SELECT CAST(record AS XML), * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
GO
-- Retrieve the CPU utilization over time
SELECT
EventTime,
n.value('(SystemIdle)[1]', 'int') AS 'CPU_Idle',
n.value('(ProcessUtilization)[1]', 'int') AS 'CPU_SQL',
100 - (n.value('(SystemIdle)[1]', 'int') +
n.value('(ProcessUtilization)[1]', 'int')) AS 'CPU_NonSQL'
FROM
(
SELECT
DATEADD(ss, (-1 * ((cpu_ticks / CONVERT(FLOAT, (cpu_ticks /
ms_ticks))) - [timestamp]) / 1000), GETDATE()) AS 'EventTime',
CONVERT(XML, record) AS 'record'
FROM sys.dm_os_ring_buffers
CROSS JOIN sys.dm_os_sys_info
WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR') AS t
CROSS APPLY
record.nodes('/Record/SchedulerMonitorEvent/SystemHealth'
) AS q(n)
ORDER BY EventTime
GO
-- Retrieve data from the RING_BUFFER_EXCEPTION ring buffer
SELECT CAST(record AS XML), * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_EXCEPTION'
GO
-- Error Exceptions
SELECT
COUNT(*) AS 'Count',
'RING_BUFFER_EXCEPTION' AS 'Type',
t.[error],
m.text AS 'ErrorMessage'
FROM
(
RingBuffer.Record.value('Error[1]', 'int') AS 'Error'
FROM
(
SELECT
CAST(Record AS XML) AS 'TargetData '
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_EXCEPTION'
) AS Data
CROSS APPLY TargetData.nodes('/Record/Exception') AS
RingBuffer(Record)
) t
LEFT JOIN sys.messages m
ON t.[error] = m.message_id
AND m.[language_id] = SERVERPROPERTY('LCID')
GROUP BY m.[text], t.[error]
GO
-- Retrieve data from the RING_BUFFER_CONNECTIVITY ring buffer
SELECT CAST(record AS XML), * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'
GO
-- Connectivity issues and timers
SELECT
record.value('(Record/@id)[1]', 'int') AS 'ID',
record.value('(Record/@type)[1]', 'varchar(50)') AS 'Type',
EventTime,
n.value('(RecordType)[1]', 'varchar(50)') AS 'RecordType',
n.value('(RecordSource)[1]', 'varchar(50)') AS 'RecordSource',
n.value('(Spid)[1]', 'int') AS 'SPID',
n.value('(SniConnectionid)[1]', 'uniqueidentifier') AS
'SniConnectionID',
n.value('(SniProvider)[1]', 'int') AS 'SniProvider',
n.value('(OSError)[1]', 'int') AS 'OSError',
n.value('(SniConsumerError)[1]', 'int') AS 'SniConsumerError',
n.value('(State)[1]', 'int') AS 'State',
n.value('(RemoteHost)[1]', 'varchar(50)') AS 'RemoteHost',
n.value('(RemotePort)[1]', 'varchar(50)') AS 'RemotePort',
n.value('(LocalHost)[1]', 'varchar(50)') AS 'LocalHost',
n.value('(LocalPort)[1]', 'varchar(50)') AS 'LocalPort',
n.value('(RecordTime)[1]', 'datetime') AS 'RecordTime',
n.value('(LoginTimers/TotalLoginTimeinMilliseconds)[1]',
'bigint') AS 'TotalLoginTimeinMilliseconds',
n.value('(LoginTimers/LoginTaskEnqueuedinMilliseconds)[1]',
'bigint') AS 'LoginTaskEnqueuedinMilliseconds',
n.value('(LoginTimers/NetworkWritesinMilliseconds)[1]',
'bigint') AS 'NetworkWritesinMilliseconds',
n.value('(LoginTimers/NetworkReadsinMilliseconds)[1]',
'bigint') AS 'NetworkReadsinMilliseconds',
n.value('(LoginTimers/SslProcessinginMilliseconds)[1]',
'bigint') AS 'SslProcessinginMilliseconds',
n.value('(LoginTimers/SspiProcessinginMilliseconds)[1]',
'bigint') AS 'SspiProcessinginMilliseconds',
n.value('(LoginTimers/LoginTriggerAndResourceGovernorProcessingi
nMilliseconds)[1]',
'bigint') AS
'LoginTriggerAndResourceGovernorProcessinginMilliseconds',
n.value('(TdsBuffersinformation/TdsinputBufferError)[1]',
'int') AS 'TdsinputBufferError',
n.value('(TdsBuffersinformation/TdsOutputBufferError)[1]',
'int') AS 'TdsOutputBufferError',
n.value('(TdsBuffersinformation/TdsinputBufferBytes)[1]',
'int') AS 'TdsinputBufferBytes',
n.value('(TdsDisconnectFlags/PhysicalConnectionisKilled)[1]',
'int') AS 'PhysicalConnectionisKilled',
n.value('(TdsDisconnectFlags/DisconnectDueToReadError)[1]',
'int') AS 'DisconnectDueToReadError',
n.value('(TdsDisconnectFlags/NetworkErrorFoundininputStream)[1]'
,
'int') AS 'NetworkErrorFoundininputStream',
n.value('(TdsDisconnectFlags/ErrorFoundBeforeLogin)[1]', 'int')
AS 'ErrorFoundBeforeLogin',
n.value('(TdsDisconnectFlags/SessionisKilled)[1]', 'int') AS
'SessionisKilled',
n.value('(TdsDisconnectFlags/NormalDisconnect)[1]', 'int') AS
'NormalDisconnect',
n.value('(TdsDisconnectFlags/NormalLogout)[1]', 'int') AS
'NormalLogout'
FROM
(
SELECT
DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (FLOAT, (cpu_ticks /
ms_ticks))) - [timestamp]) / 1000), GETDATE()) AS 'EventTime',
CONVERT (xml, record) AS 'record'
FROM sys.dm_os_ring_buffers
CROSS JOIN sys.dm_os_sys_info
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'
) AS tab
CROSS APPLY record.nodes('/Record/ConnectivityTraceRecord') AS
x(n)
ORDER BY RecordTime
GO
-- Retrieve data from the RING_BUFFER_MEMORY_BROKER ring buffer
SELECT CAST(record AS XML), * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER'
GO
-- Retrieve memory utilization
-- => MEMORYBROKER_FOR_CACHE: Memory that is allocated for use
by cached objects
-- => MEMORYBROKER_FOR_STEAL: Memory that is stolen from the
buffer pool. This memory is not available for reuse by other
components until it is freed by the current owner
-- => MEMORYBROKER_FOR_RESERVE: Memory reserved for future use
by currently executing requests
SELECT
EventTime,
n.value('(Pool)[1]', 'int') AS 'Pool',
n.value('(Broker)[1]', 'varchar(40)') AS 'Broker',
n.value('(Notification)[1]', 'varchar(40)') AS
'Notification',
n.value('(MemoryRatio)[1]', 'int') AS 'MemoryRatio',
n.value('(NewTarget)[1]', 'int') AS 'NewTarget',
n.value('(Overall)[1]', 'int') AS 'Overall',
n.value('(Rate)[1]', 'int') AS 'Rate',
n.value('(CurrentlyPredicted)[1]', 'int') AS
'CurrentlyPredicted',
n.value('(CurrentlyAllocated)[1]', 'int') AS
'CurrentlyAllocated'
FROM
(
SELECT
DATEADD(ss, (-1 * ((cpu_ticks / CONVERT (FLOAT, (cpu_ticks /
ms_ticks))) - [timestamp]) / 1000), GETDATE()) AS EventTime,
CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
CROSS JOIN sys.dm_os_sys_info
WHERE ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER'
) AS t
CROSS APPLY record.nodes('/Record/MemoryBroker') AS x(n)
ORDER BY EventTime
GO
-- Retrieve data from the RING_BUFFER_OOM ring buffer
SELECT CAST(record AS XML), * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_OOM'
GO
-- Retrieve Out of Memory Notifications
SELECT
EventTime,
n.value('(OOM/Action)[1]', 'varchar(50)') AS 'Action',
n.value('(OOM/Resources)[1]', 'int') AS 'Resources',
n.value('(OOM/Task)[1]', 'varchar(20)') AS 'Task',
n.value('(OOM/Pool)[1]', 'int') AS 'PoolID',
n.value('(MemoryRecord/MemoryUtilization)[1]', 'int') AS
'MemoryUtilization',
n.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'int') AS
'AvailablePhysicalMemory',
n.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]',
'int') AS 'AvailableVirtualAddressSpace'
FROM
(
SELECT
DATEADD(ss, (-1 * ((cpu_ticks / CONVERT (FLOAT, (cpu_ticks /
ms_ticks))) - [timestamp]) / 1000), GETDATE()) AS EventTime,
CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
CROSS JOIN sys.dm_os_sys_info
WHERE ring_buffer_type = 'RING_BUFFER_OOM'
) AS t
CROSS APPLY record.nodes('/Record') AS x(n)
GO
-- Retrieve data from the RING_BUFFER_SECURITY_ERROR ring buffer
SELECT CAST(record AS XML), * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR'
GO
-- Retrieve data from the RING_BUFFER_SECURITY_ERROR ring buffer
SELECT
CONVERT(VARCHAR(30), GETDATE(), 121) AS 'Runtime',
DATEADD(ms, (t.RecordTime - sys.ms_ticks), GETDATE()) AS
'NotificationTime',
t.* ,
sys.ms_ticks AS 'CurrentTime'
FROM
(
SELECT
x.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS
'ErrorCode',
x.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)')
AS 'CallingAPIName',
x.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS
'APIName',
x.value('(//Record/Error/SPID)[1]', 'int') AS 'SPID',
x.value('(//Record/@id)[1]', 'bigint') AS 'RecordID',
x.value('(//Record/@type)[1]', 'varchar(30)') AS 'Type',
x.value('(//Record/@time)[1]', 'bigint') AS 'RecordTime'
FROM
(
SELECT
CAST (record AS XML)
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR'
) AS R(x)
) t
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY t.RecordTime
GO
*****************************************************