info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
sp_server_diagnostics
by BF (Principal Consultant; Architecture; Engineering)
2017-06-07
T-SQL:
Get the lastest record from sp_server_diagnostics
CREATE TABLE #ServerDiagnostics
(create_time DateTime, component_type sysname, component_name sysname, [state] int, state_desc sysname, data xml
)
INSERT INTO #ServerDiagnostics EXEC sp_server_diagnostics
SELECT * FROM #ServerDiagnostics
Parse:
System:
select 'System' as "System",
data.value('(/system/@spinlockBackoffs)[1]','bigint') as "Spin Lock Backoffs",
data.value('(/system/@sickSpinlockType)[1]','nvarchar(100)') as "Spin Lock Type",
data.value('(/system/@spinlockBackoffs)[1]','bigint') as "Spin Lock Backoffs",
data.value('(/system/@systemCpuUtilization)[1]','bigint') as "System CPU Utilization",
data.value('(/system/@sqlCpuUtilization)[1]','bigint') as "SQL CPU Utilization",
data.value('(/system/@nonYieldingTasksReported)[1]','bigint') as "Non-yielding Tasks",
data.value('(/system/@pageFaults)[1]','bigint') as "Page Faults",
data.value('(/system/@BadPagesDetected)[1]','bigint') as "Bad Pages Detected",
data.value('(/system/@latchWarnings)[1]','bigint') as "Latch Warnings"
from #ServerDiagnostics
where component_name like 'system'
DROP TABLE #ServerDiagnostics
Get the full records for sp_server_diagnostics stored in System Health Extended Events Files
/*
Get sp_server_diagnostics data from the System Health Extended Event Sessions .XEL files stored in the SQL Server instance Log folder.
*/
SET NOCOUNT ON
Declare @Debug bit = 1
DROP TABLE #tbl_sp_server_diagnostics
IF (SUBSTRING(CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)),1,CHARINDEX('.',CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)))-1) >= 11)
BEGIN
Select SERVERPROPERTY ('ProductVersion') as 'Product Version'
DECLARE @UTDDateDiff int
SET @UTDDateDiff = DATEDIFF(mi,GETUTCDATE(),GETDATE())
-- Fetch information about the XEL file location
DECLARE @filename varchar(8000) ;
SELECT @filename = CAST(target_data as XML).value('(/EventFileTarget/File/@name)[1]', 'varchar(8000)')
FROM sys.dm_xe_session_targets
WHERE target_name = 'event_file' and event_session_address = (select address from sys.dm_xe_sessions where name = 'system_health');
If @Debug = 1 Begin Select @filename as 'File Name' End
SET @filename = SUBSTRING(@filename,1,CHARINDEX('system_health',@filename,1)-1) + '*.xel';
If @Debug = 1 Begin Select @filename as 'File Name - Parsed - in order to process all existing System Health Extended Events Files (.xel) files' End
-- Read the XEL files to get the System Health Session Data
SELECT object_name,CAST(event_data as XML) as XMLData
INTO #tbl_sp_server_diagnostics
FROM sys.fn_xe_file_target_read_file(@filename, null, null, null)
WHERE object_name = 'sp_server_diagnostics_component_result'
If @Debug = 1 Begin
Select count(*) as 'Total Events from all System Health Extended Events Files (.xel) (Record Data Interval - 5 mins)' from #tbl_sp_server_diagnostics
--SELECT Object_Name,CAST(event_data as XML) as [Raw XML Data] FROM sys.fn_xe_file_target_read_file(@filename, null, null, null) WHERE object_name = 'sp_server_diagnostics_component_result'
End
SELECT
'Component State' as [sp_server_diagnostics component],
DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime')) as EventTime,
XMLData.value('(/event/data/text)[1]','varchar(255)') as Component,
XMLData.value('(/event/data/text)[2]','varchar(255)') as [State]
FROM #tbl_sp_server_diagnostics
--WHERE XMLData.value('(/event/data/text)[2]','varchar(255)') <> 'CLEAN'
ORDER BY EventTime DESC
SELECT
'SYSTEM' as [sp_server_diagnostics component],
Count(*) as 'Total Events',
MIN(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime'))) as [Min_Event Time],
MAX(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime'))) as [Max_Event Time],
DATEDIFF(hh,MIN(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime'))), MAX(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime')))) as 'Data Collection (Hours)'
FROM #tbl_sp_server_diagnostics
WHERE XMLData.value('(/event/data/text)[1]','varchar(255)') = 'SYSTEM'
SELECT
DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime')) as [Event Time],
XMLData.value('(/event/data/text)[1]','varchar(255)') as Component,
XMLData.value('(/event/data/value/system/@latchWarnings)[1]','bigint') as [Latch Warnings],
XMLData.value('(/event/data/value/system/@isAccessViolationOccurred)[1]','bigint') as [Access Violations],
XMLData.value('(/event/data/value/system/@nonYieldingTasksReported)[1]','bigint') as [Non Yields Reported],
XMLData.value('(/event/data/value/system/@pageFaults)[1]','bigint') as [Page Faults],
XMLData.value('(/event/data/value/system/@systemCpuUtilization)[1]','int') as [System CPU Utilization %],
XMLData.value('(/event/data/value/system/@sqlCpuUtilization)[1]','int') as [SQL CPU Utilization %],
XMLData.value('(/event/data/value/system/@BadPagesDetected)[1]','bigint') as [Bad Pages Detected],
XMLData.value('(/event/data/value/system/@BadPagesFixed)[1]','bigint') as [Bad Pages Fixed]
FROM #tbl_sp_server_diagnostics
WHERE XMLData.value('(/event/data/text)[1]','varchar(255)') = 'SYSTEM'
ORDER BY [Event Time] DESC
SELECT
'QUERY_PROCESSING' as [sp_server_diagnostics component],
Count(*) as 'Total Events',
MIN(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime'))) as [Min_Event Time],
MAX(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime'))) as [Max_Event Time],
DATEDIFF(hh,MIN(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime'))), MAX(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime')))) as 'Data Collection (Hours)'
FROM #tbl_sp_server_diagnostics
WHERE XMLData.value('(/event/data/text)[1]','varchar(255)') = 'QUERY_PROCESSING'
SELECT
DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime')) as [Event Time],
XMLData.value('(/event/data/text)[1]','varchar(255)') as Component,
XMLData.value('(/event/data/value/queryProcessing/@maxWorkers)[1]','bigint') as [Max Workers],
XMLData.value('(/event/data/value/queryProcessing/@workersCreated)[1]','bigint') as [Workers Created],
XMLData.value('(/event/data/value/queryProcessing/@workersIdle)[1]','bigint') as [Idle Workers],
XMLData.value('(/event/data/value/queryProcessing/@pendingTasks)[1]','bigint') as [Pending Tasks],
XMLData.value('(/event/data/value/queryProcessing/@hasUnresolvableDeadlockOccurred)[1]','int') as [Unresolvable Deadlock],
XMLData.value('(/event/data/value/queryProcessing/@hasDeadlockedSchedulersOccurred)[1]','int') as [Deadlocked Schedulers]
FROM #tbl_sp_server_diagnostics
WHERE XMLData.value('(/event/data/text)[1]','varchar(255)') = 'QUERY_PROCESSING'
ORDER BY [Event Time] DESC
SELECT
'RESOURCE' as [sp_server_diagnostics component],
Count(*) as 'Total Events',
MIN(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime'))) as [Min_Event Time],
MAX(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime'))) as [Max_Event Time],
DATEDIFF(hh,MIN(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime'))), MAX(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime')))) as 'Data Collection (Hours)'
FROM #tbl_sp_server_diagnostics
WHERE XMLData.value('(/event/data/text)[1]','varchar(255)') = 'RESOURCE'
SELECT
DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime')) as [Event Time],
XMLData.value('(/event/data/text)[1]','varchar(255)') as Component,
XMLData.value('(/event/data/value/resource/@outOfMemoryExceptions)[1]','bigint') as [OOM Exceptions],
XMLData.value('(/event/data/value/resource/memoryReport/entry/@value)[1]','bigint')/(1024*1024*1024) as [Available Physical Memory (GB)],
XMLData.value('(/event/data/value/resource/memoryReport/entry/@value)[3]','bigint')/(1024*1024*1024) as [Available Paging File (GB)],
XMLData.value('(/event/data/value/resource/memoryReport/entry/@value)[5]','int') as [Percent of Committed Memory in WS],
XMLData.value('(/event/data/value/resource/memoryReport/entry/@value)[6]','bigint') as [Page Faults],
XMLData.value('(/event/data/value/resource/memoryReport/entry/@value)[12]','bigint')/1024 as [VM Committed (MB)],
XMLData.value('(/event/data/value/resource/memoryReport/entry/@value)[13]','bigint')/(1024*1024) as [Locked Pages Allocated (GB)],
XMLData.value('(/event/data/value/resource/memoryReport/entry/@value)[14]','bigint')/(1024*1024) as [Large Pages Allocated (GB)],
XMLData.value('(/event/data/value/resource/memoryReport/entry/@value)[17]','bigint')/(1024*1024) as [Target Committed (GB)],
XMLData.value('(/event/data/value/resource/memoryReport/entry/@value)[18]','bigint')/(1024*1024) as [Current Committed (GB)],
XMLData.value('(/event/data/value/resource/memoryReport/entry/@value)[19]','bigint') as [Pages Allocated],
XMLData.value('(/event/data/value/resource/memoryReport/entry/@value)[20]','bigint') as [Pages Reserved],
XMLData.value('(/event/data/value/resource/memoryReport/entry/@value)[21]','bigint') as [Pages Free],
XMLData.value('(/event/data/value/resource/memoryReport/entry/@value)[22]','bigint') as [Pages In Use]
FROM #tbl_sp_server_diagnostics
WHERE XMLData.value('(/event/data/text)[1]','varchar(255)') = 'RESOURCE'
ORDER BY [Event Time] DESC
SELECT
'IO_SUBSYSTEM' as [sp_server_diagnostics component],
Count(*) as 'Total Events',
MIN(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime'))) as [Min_Event Time],
MAX(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime'))) as [Max_Event Time],
DATEDIFF(hh,MIN(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime'))), MAX(DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime')))) as 'Data Collection (Hours)'
FROM #tbl_sp_server_diagnostics
WHERE XMLData.value('(/event/data/text)[1]','varchar(255)') = 'IO_SUBSYSTEM'
SELECT
DATEADD(mi,@UTDDateDiff,XMLData.value('(/event/@timestamp)[1]','datetime')) as [Event Time],
XMLData.value('(/event/data/text)[1]','varchar(255)') as Component,
XMLData.value('(/event/data/value/ioSubsystem/@ioLatchTimeouts)[1]','bigint') as [IO Latch Timeouts],
XMLData.value('(/event/data/value/ioSubsystem/@totalLongIos)[1]','bigint') as [Total Long IOs],
XMLData.value('(/event/data/value/ioSubsystem/longestPendingRequests/pendingRequest/@filePath)[1]','varchar(8000)') as [Longest Pending Request File],
XMLData.value('(/event/data/value/ioSubsystem/longestPendingRequests/pendingRequest/@duration)[1]','bigint') as [Longest Pending IO Duration]
FROM #tbl_sp_server_diagnostics
WHERE XMLData.value('(/event/data/text)[1]','varchar(255)') = 'IO_SUBSYSTEM'
ORDER BY [Event Time] DESC
END
SET NOCOUNT OFF
|
|
|
|
|
|
|
|
|
|