sp_server_diagnostics (Transact-SQL)

Статья
04/10/2024

Применимо к:SQL Server

Это событие служит для записи диагностических данных и сведений о работоспособности SQL Server с целью выявления потенциальных сбоев. Процедура работает в повторяющемся режиме и периодически отправляет результаты. Его можно вызвать из регулярного подключения или выделенного подключения администратора.

Соглашения о синтаксисе Transact-SQL

Синтаксис

sp_server_diagnostics [ @repeat_interval = ] 'repeat_interval'
[ ; ]

Аргументы

[ @repeat_interval = ] 'repeat_interval'

Указывает интервал времени, с которым хранимая процедура выполняется многократно для отправки сведений о работоспособности.

@repeat_interval имеет значение по умолчанию0. Допустимые значения параметров : 0или любое значение, равное или больше 5. Чтобы вернуть полные данные, хранимая процедура должна работать не менее 5 секунд. Минимальное значение для выполнения хранимой процедуры в режиме повтора равно 5 секундам.

Если этот параметр не указан или если задано указанное значение 0, хранимая процедура возвращает данные один раз, а затем выйти.

Если указанное значение меньше минимального значения, оно вызывает ошибку и возвращает ничего.

Если указанное значение равно или больше 5, хранимая процедура выполняется многократно, чтобы вернуть состояние работоспособности до тех пор, пока не будет отменено вручную.

Значения кода возврата

0 (успешно) или 1 (сбой).

Результирующий набор

sp_server_diagnostics возвращает следующие сведения.

Column	Тип данных	Description
`create_time`	datetime	Указывает отметку времени создания строки. Все строки в одном наборе данных имеют одинаковые отметки времени.
`component_type`	sysname	Указывает, содержит ли строка сведения для компонента уровня экземпляра SQL Server или для группы доступности AlwaysOn: `instance` `Always On:AvailabilityGroup`
`component_name`	sysname	Указывает имя компонента или имя группы доступности: `system` `resource` `query_processing` `io_subsystem` `events` `<name of the availability group>`
`state`	int	Указывает состояние работоспособности компонента. Может быть одним из следующих значений: `0`, `1`, `2`или `3`
`state_desc`	sysname	Описывает столбец state. Далее представлены описания, соответствующие значениям в столбце state: 0: `Unknown` 1: `clean` 2: `warning` 3: `error`
`data`	varchar (max)	Указывает данные, свойственные данному компоненту.

Далее даны описания пяти компонентов.

система: собирает данные с точки зрения системы на спин-блокировки, тяжелые условия обработки, неоправдающие задачи, ошибки страниц и использование ЦП. Эти сведения представляют рекомендации по состоянию работоспособности.
ресурс: собирает данные с точки зрения ресурсов на физической и виртуальной памяти, буферных пулах, страницах, кэше и других объектах памяти. Эти сведения представляют рекомендации по состоянию работоспособности.
query_processing. Собирает данные с точки зрения обработки запросов на рабочие потоки, задачи, типы ожидания, интенсивные сеансы ЦП и блокирующие задачи. Эти сведения представляют рекомендации по состоянию работоспособности.
io_subsystem. Собирает данные в операции ввода-вывода. Помимо диагностических данных, этот компонент передает состояние удовлетворительной работоспособности или предупреждение работоспособности только для подсистемы ввода-вывода.
события: собирает данные и поверхности с помощью хранимой процедуры по ошибкам и событиям, интересующим сервер, включая сведения об исключениях из буфера кольца, событиях буфера кольца о брокере памяти, вне памяти, мониторе планировщика, буферном пуле, спинлоках, безопасности и подключении. События всегда отображаются 0 как состояние.
<имя группы> доступности: собирает данные для указанной группы доступности (если component_type = "Always On:AvailabilityGroup").

Замечания

С точки зрения systemresourceсбоя компоненты и query_processing компоненты используются для обнаружения сбоев, а io_subsystemevents компоненты используются только для диагностических целей.

В следующей таблице представлены компоненты и связанные с ними состояния работоспособности.

Компоненты	Удовлетворительно (1)	Предупреждение (2)	Ошибка (3)	Неизвестно (0)
`system`	x	x	x
`resource`	x	x	x
`query_processing`	x	x	x
`io_subsystem`	x	x
`events`				x

В x каждой строке представлены допустимые состояния работоспособности компонента. Например, io_subsystem отображается как clean или warning. В нем не отображаются состояния ошибок.

Примечание.

Внутренняя sp_server_diagnostics процедура реализуется в преемтивном потоке с высоким приоритетом.

Разрешения

Необходимо разрешение VIEW SERVER STATE на сервере.

Примеры

Рекомендуется использовать сеансы расширенных событий для записи сведений о работоспособности и сохранения его в файл, расположенный за пределами SQL Server. Таким образом, вы по-прежнему можете получить доступ к нему, если произошел сбой.

А. Сохранение выходных данных сеанса расширенных событий в файл

В следующем примере выходные данные сеанса событий сохраняются в файл:

CREATE EVENT SESSION [diag]
ON SERVER
    ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)
    ADD TARGET [asynchronous_file_target] (set filename='C:\temp\diag.xel');
GO
ALTER EVENT SESSION [diag]
    ON SERVER STATE = start;
GO

B. Чтение журнала сеансов расширенных событий

Следующий запрос считывает файл журнала сеансов расширенных событий в SQL Server 2016 (13.x):

SELECT xml_data.value('(/event/@name)[1]', 'varchar(max)') AS Name,
    xml_data.value('(/event/@package)[1]', 'varchar(max)') AS Package,
    xml_data.value('(/event/@timestamp)[1]', 'datetime') AS 'Time',
    xml_data.value('(/event/data[@name=''component_type'']/value)[1]', 'sysname') AS SYSNAME,
    xml_data.value('(/event/data[@name=''component_name'']/value)[1]', 'sysname') AS Component,
    xml_data.value('(/event/data[@name=''state'']/value)[1]', 'int') AS STATE,
    xml_data.value('(/event/data[@name=''state_desc'']/value)[1]', 'sysname') AS State_desc,
    xml_data.query('(/event/data[@name="data"]/value/*)') AS Data
FROM (
    SELECT object_name AS event,
        CONVERT(XML, event_data) AS xml_data
    FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\*.xel', NULL, NULL, NULL)
) AS XEventData
ORDER BY TIME;

C. Запись `sp_server_diagnostics` выходных данных в таблицу

В следующем примере выходные данные 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;

D. Чтение подробных выходных данных каждого компонента

В следующем примере запросы считывают некоторые подробные выходные данные каждого компонента в таблице, созданной в предыдущем примере.

Система:

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

Монитор ресурсов:

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

Нерекоментивные ожидания:

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

Ожидание предварительного ожидания:

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

Интенсивные запросы ЦП:

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

Отчет о заблокированных процессах:

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

Входные и выходные данные:

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

Сведения о событии:

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

Failover Policy for Failover Cluster Instances

sp_server_diagnostics (Transact-SQL)

Синтаксис

Аргументы

[ @repeat_interval = ] 'repeat_interval'

Значения кода возврата

Результирующий набор

Замечания

Разрешения

Примеры

А. Сохранение выходных данных сеанса расширенных событий в файл

B. Чтение журнала сеансов расширенных событий

C. Запись `sp_server_diagnostics` выходных данных в таблицу

D. Чтение подробных выходных данных каждого компонента

Обратная связь

Обратная связь

Дополнительные ресурсы

sp_server_diagnostics (Transact-SQL)

Синтаксис

Аргументы

[ @repeat_interval = ] 'repeat_interval'

Значения кода возврата

Результирующий набор

Замечания

Разрешения

Примеры

А. Сохранение выходных данных сеанса расширенных событий в файл

B. Чтение журнала сеансов расширенных событий

C. Запись sp_server_diagnostics выходных данных в таблицу

D. Чтение подробных выходных данных каждого компонента

Связанный контент

Обратная связь

Обратная связь

Дополнительные ресурсы

C. Запись `sp_server_diagnostics` выходных данных в таблицу