Introduction
Introduction Statistics Contact Development Disclaimer Help
Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
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
*****************************************************
You are viewing proxied material from gopher.createaforum.com. The copyright of proxied material belongs to its original authors. Any comments or complaints in relation to proxied material should be directed to the original authors of the content concerned. Please see the disclaimer for more details.