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

Тип ожидания PREEMPTIVE_OS_PIPEOPS является общим ожиданием, вызванным действиями, связанными с каналами SQL, такими как xp_cmdshell. Когда клиент обратился ко мне за помощью с этим типом ожидания, мне нужно было выяснить причину проблемы.

Расширенные события (XEvents) – это мощный инструмент, который может помочь нам отслеживать и контролировать типы ожидания. Первый шаг – определить тип ожидания, который требует внимания. В данном случае мы знаем, что ожидание PREEMPTIVE_OS_PIPEOPS является внешним ожиданием, поэтому мы будем использовать событие sqlos.wait_info_external.

Однако вызовом является то, что целочисленное значение для типа ожидания может изменяться от накопительного обновления (CU) к CU SQL Server. Чтобы найти правильное целочисленное значение, мы можем запросить динамическое управление представлением sys.dm_xe_map_values (DMV). Это даст нам необходимую информацию для создания сеанса событий для отслеживания конкретного типа ожидания.

Вот пример запроса представления sys.dm_xe_map_values DMV для поиска целочисленного значения типа ожидания PREEMPTIVE_OS_PIPEOPS:

SELECT *
FROM sys.dm_xe_map_values ws
WHERE ws.map_value = 'PREEMPTIVE_OS_PIPEOPS'
AND ws.name = 'wait_types';

После получения целочисленного значения мы можем создать сеанс событий с использованием расширенных событий. Вот пример создания сеанса событий для отслеживания типа ожидания PREEMPTIVE_OS_PIPEOPS:

USE master;
GO
CREATE EVENT SESSION [PREEMPTIVE_OS_PIPEOPS]
ON SERVER
ADD EVENT sqlos.wait_info_external
(ACTION
 (
 package0.callstack
   , package0.collect_system_time
   , package0.event_sequence
   , sqlserver.client_app_name
   , sqlserver.client_connection_id
   , sqlserver.client_hostname
   , sqlserver.context_info
   , sqlserver.database_id
   , sqlserver.database_name
   , sqlserver.nt_username
   , sqlserver.server_principal_name
   , sqlserver.session_id
   , sqlserver.session_nt_username
   , sqlserver.sql_text
   , sqlserver.username
 )
 WHERE ([wait_type] = 'PREEMPTIVE_OS_PIPEOPS')
)
ADD TARGET package0.event_file
(SET filename = N'C:\Database\XE\PREEMPTIVE_OS_PIPEOPS.xel')
WITH
(
MAX_MEMORY = 4096KB
  , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
  , MAX_DISPATCH_LATENCY = 5 SECONDS
  , TRACK_CAUSALITY = ON
  , STARTUP_STATE = ON
);
GO

После выполнения этого скрипта сеанс событий будет создан и запущен. Чтобы проверить его работу, вы можете выполнить запрос, который вызывает команду xp_cmdshell.

Отслеживая сеанс событий, мы можем определить причину типа ожидания PREEMPTIVE_OS_PIPEOPS и принять соответствующие меры для устранения проблемы.

Следует отметить, что значение map_key для типов ожидания может изменяться при обновлениях SQL Server. Однако есть более простой способ создания сеанса событий без необходимости беспокоиться о значении map_key. Этот метод фактически используется в системном сеансе system_health по умолчанию.

Вот пример упрощенного скрипта для создания сеанса событий:

USE master;
GO
-- Создание сеанса событий
IF EXISTS
(
SELECT
  *
FROM  sys.server_event_sessions
WHERE name = 'PREEMPTIVE_OS_PIPEOPS'
)
DROP EVENT SESSION PREEMPTIVE_OS_PIPEOPS ON SERVER;
GO
CREATE EVENT SESSION [PREEMPTIVE_OS_PIPEOPS]
ON SERVER
ADD EVENT sqlos.wait_info_external
(ACTION
 (
 package0.callstack
   , package0.collect_system_time
   , package0.event_sequence
   , sqlserver.client_app_name
   , sqlserver.client_connection_id
   , sqlserver.client_hostname
   , sqlserver.context_info
   , sqlserver.database_id
   , sqlserver.database_name
   , sqlserver.nt_username
   , sqlserver.server_principal_name
   , sqlserver.session_id
   , sqlserver.session_nt_username
   , sqlserver.sql_text
   , sqlserver.username
 )
 WHERE ([wait_type] = 'PREEMPTIVE_OS_PIPEOPS')
)
ADD TARGET package0.event_file
(SET filename = N'C:\Database\XE\PREEMPTIVE_OS_PIPEOPS.xel')
WITH
(
MAX_MEMORY = 4096KB
  , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
  , MAX_DISPATCH_LATENCY = 5 SECONDS
  , TRACK_CAUSALITY = ON
  , STARTUP_STATE = ON
);
GO

Использование расширенных событий для отслеживания типов ожидания предоставляет администраторам баз данных мощный и гибкий инструмент для устранения проблем с производительностью. Следуя описанным в этой статье шагам, вы можете эффективно исследовать и определить причину конкретных типов ожидания в SQL Server.

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