Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



sp_server_diagnostics
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-06-07









T-SQL:


Capture:

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/@systemCpuUtilization)[1]','bigint') as "System CPU",
data.value('(/system/@sqlCpuUtilization)[1]','bigint') as "SQL CPU",
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