SQL Server系统诊断之sp_server_diagnostics存储过程

由小麦苗 · 2023年2月16日

0 29 1

Tags： MSSQL sp_server_diagnostics SQL Server 诊断

👉 本文共约1008个字，系统预计阅读时间或需4分钟。

本页目录 隐藏

简介

SQL Server 2012中引入了系统存储过程sys.sp_server_diagnostics，该存储过程通过捕获并返回方便地归类给我们的与运行状况和事件相关的信息来提供对SQL实例的快速评估。

 EXEC sp_server_diagnostics

1	EXEC sp_server_diagnostics

存储过程也可以在重复模式下执行，在该模式下可以经过一个时间间隔，并且执行将连续重复（直到取消），并且每个指定的间隔持续时间将输出发送到结果窗格：


  EXEC sp_server_diagnostics 5

EXEC sp_server_diagnostics 5

结果解析

下面是对五个组件的说明：

本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

system：从系统角度收集有关旋转锁、恶劣处理条件、无结果任务、页面错误和 CPU 使用率的数据。此信息会产生总体运行状态建议。
资源：从资源角度收集有关物理和虚拟内存、缓冲池、页、缓存和其他内存对象的数据。此信息会提供总体运行状态建议。
query_processing：从查询处理角度收集有关工作线程、任务、等待类型、CPU 密集型会话和阻止任务的数据。此信息会提供总体运行状态建议。
io_subsystem：收集有关 IO 的数据。除了诊断数据外，此组件还可生成仅适用于 IO 子系统的干净运行状况或警告运行状态。
事件：通过存储过程收集有关服务器记录的错误和事件的数据和图面，包括有关环形缓冲区异常、有关内存代理的环形缓冲区事件、内存不足、计划程序监视器、缓冲池、旋转锁、安全性和连接的详细信息。事件将始终显示 0 作为状态。

结合表使用

以下示例以非重复模式将 sp_server_diagnostics 的输出捕获到一个表中：

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;

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;

下面的示例查询读取表格的摘要输出：

SELECT create_time,
       component_name,
       state_desc 
FROM SpServerDiagnosticsResult;

SELECT create_time,

component_name,

state_desc

FROM SpServerDiagnosticsResult;

下面的示例查询读取表格中每个组件的部分详细输出：

-- 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

-- 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'

-- 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'

-- 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'

-- 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'

-- 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'

-- 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'

-- 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'

-- 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'

参考

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

打赏赞(1)

标签： MSSQL sp_server_diagnostics SQL Server 诊断

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

2023年 3月
一	二	三	四	五	六	日
	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

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

小麦苗

SQL Server系统诊断之sp_server_diagnostics存储过程

简介

结果解析

结合表使用

参考

相关文章

您可能还喜欢...

发表回复取消回复

博客寄语

博客概况

搜标题

搜索本网站

标签云☁

博客日历

博客归档

博客分类

SQL Server系统诊断之sp_server_diagnostics存储过程

简介

结果解析

结合表使用

参考

相关文章

您可能还喜欢...

SQL Server中的即席查询

MSSQL查询前几张大表

排查 IO 问题导致的SQL Server性能缓慢问题

发表回复 取消回复

博客寄语

博客概况

搜标题

搜索本网站

标签云☁

博客日历

博客归档

博客分类

发表回复取消回复