SQL Server系统诊断之sp_server_diagnostics存储过程
Tags: MSSQLsp_server_diagnosticsSQL Server诊断
简介
SQL Server 2012中引入了系统存储过程sys.sp_server_diagnostics, 该存储过程通过捕获并返回方便地归类给我们的与运行状况和事件相关的信息来提供对SQL实例的快速评估。
1 | EXEC sp_server_diagnostics |
存储过程也可以在重复模式下执行,在该模式下可以经过一个时间间隔,并且执行将连续重复(直到取消),并且每个指定的间隔持续时间将输出发送到结果窗格:
1 2 3 | EXEC sp_server_diagnostics 5 |
结果解析
下面是对五个组件的说明:
- system:从系统角度收集有关旋转锁、恶劣处理条件、无结果任务、页面错误和 CPU 使用率的数据。 此信息会产生总体运行状态建议。
- 资源:从资源角度收集有关物理和虚拟内存、缓冲池、页、缓存和其他内存对象的数据。 此信息会提供总体运行状态建议。
- query_processing:从查询处理角度收集有关工作线程、任务、等待类型、CPU 密集型会话和阻止任务的数据。 此信息会提供总体运行状态建议。
- io_subsystem:收集有关 IO 的数据。 除了诊断数据外,此组件还可生成仅适用于 IO 子系统的干净运行状况或警告运行状态。
- 事件:通过存储过程收集有关服务器记录的错误和事件的数据和图面,包括有关环形缓冲区异常、有关内存代理的环形缓冲区事件、内存不足、计划程序监视器、缓冲池、旋转锁、安全性和连接的详细信息。 事件将始终显示 0 作为状态。
结合表使用
以下示例以非重复模式将 sp_server_diagnostics 的输出捕获到一个表中:
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE SpServerDiagnosticsResult ( create_time DateTime, component_type sysname, component_name sysname, state int, state_desc sysname, data xml ); INSERT INTO SpServerDiagnosticsResult EXEC sp_server_diagnostics; |
下面的示例查询读取表格的摘要输出:
1 2 3 4 | SELECT create_time, component_name, state_desc FROM SpServerDiagnosticsResult; |
下面的示例查询读取表格中每个组件的部分详细输出:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | -- system select 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 'NonYielding_Tasks', data.value('(/system/@pageFaults)[1]','bigint') as 'Page_Faults', data.value('(/system/@latchWarnings)[1]','bigint') as 'Latch_Warnings', data.value('(/system/@BadPagesDetected)[1]','bigint') as 'BadPages_Detected', data.value('(/system/@BadPagesFixed)[1]','bigint') as 'BadPages_Fixed' from SpServerDiagnosticsResult where component_name like 'system' go -- Resource Monitor select data.value('(./Record/ResourceMonitor/Notification)[1]', 'VARCHAR(max)') AS [Notification], data.value('(/resource/memoryReport/entry[@description=''Working Set'']/@value)[1]', 'bigint')/1024 AS [SQL_Mem_in_use_MB], data.value('(/resource/memoryReport/entry[@description=''Available Paging File'']/@value)[1]', 'bigint')/1024 AS [Avail_Pagefile_MB], data.value('(/resource/memoryReport/entry[@description=''Available Physical Memory'']/@value)[1]', 'bigint')/1024 AS [Avail_Physical_Mem_MB], data.value('(/resource/memoryReport/entry[@description=''Available Virtual Memory'']/@value)[1]', 'bigint')/1024 AS [Avail_VAS_MB], data.value('(/resource/@lastNotification)[1]','varchar(100)') as 'LastNotification', data.value('(/resource/@outOfMemoryExceptions)[1]','bigint') as 'OOM_Exceptions' from SpServerDiagnosticsResult where component_name like 'resource' go -- Nonpreemptive waits select waits.evt.value('(@waitType)','varchar(100)') as 'Wait_Type', waits.evt.value('(@waits)','bigint') as 'Waits', waits.evt.value('(@averageWaitTime)','bigint') as 'Avg_Wait_Time', waits.evt.value('(@maxWaitTime)','bigint') as 'Max_Wait_Time' from SpServerDiagnosticsResult CROSS APPLY data.nodes('/queryProcessing/topWaits/nonPreemptive/byDuration/wait') AS waits(evt) where component_name like 'query_processing' go -- Preemptive waits select waits.evt.value('(@waitType)','varchar(100)') as 'Wait_Type', waits.evt.value('(@waits)','bigint') as 'Waits', waits.evt.value('(@averageWaitTime)','bigint') as 'Avg_Wait_Time', waits.evt.value('(@maxWaitTime)','bigint') as 'Max_Wait_Time' from SpServerDiagnosticsResult CROSS APPLY data.nodes('/queryProcessing/topWaits/preemptive/byDuration/wait') AS waits(evt) where component_name like 'query_processing' go -- CPU intensive requests select cpureq.evt.value('(@sessionId)','bigint') as 'SessionID', cpureq.evt.value('(@command)','varchar(100)') as 'Command', cpureq.evt.value('(@cpuUtilization)','bigint') as 'CPU_Utilization', cpureq.evt.value('(@cpuTimeMs)','bigint') as 'CPU_Time_ms' from SpServerDiagnosticsResult CROSS APPLY data.nodes('/queryProcessing/cpuIntensiveRequests/request') AS cpureq(evt) where component_name like 'query_processing' go -- Blocked Process Report select blk.evt.query('.') as 'Blocked_Process_Report_XML' from SpServerDiagnosticsResult CROSS APPLY data.nodes('/queryProcessing/blockingTasks/blocked-process-report') AS blk(evt) where component_name like 'query_processing' go -- IO select data.value('(/ioSubsystem/@ioLatchTimeouts)[1]','bigint') as 'Latch_Timeouts', data.value('(/ioSubsystem/@totalLongIos)[1]','bigint') as 'Total_Long_IOs' from SpServerDiagnosticsResult where component_name like 'io_subsystem' go -- Event information select xevts.evt.value('(@name)','varchar(100)') as 'xEvent_Name', xevts.evt.value('(@package)','varchar(100)') as 'Package', xevts.evt.value('(@timestamp)','datetime') as 'xEvent_Time', xevts.evt.query('.') as 'Event Data' from SpServerDiagnosticsResult CROSS APPLY data.nodes('/events/session/RingBufferTarget/event') AS xevts(evt) where component_name like 'events' go |
参考
https://www.sqlshack.com/using-sp_server_diagnostics/
https://blog.csdn.net/culuo4781/article/details/107627174
https://learn.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-server-diagnostics-transact-sql?view=sql-server-2017