Автор: Paul Randal, Who is overriding MAXDOP 1 on the instance?
Мне пришёл вопрос по электронной почте: «как могут возникать ожидания латча ACCESS_METHODS_DATASET_PARENT, когда MAXDOP экземпляра установлен в 1?» Этого не должно происходить, так как эта краткая блокировка используется только для координации между потоками, выполняющими параллельное сканирование таблицы или индекса. Однако то, что MAXDOP экземпляра установлен в 1, не означает, что параллелизм не может возникнуть.
Любой может переопределить настройку MAXDOP экземпляра с помощью подсказки запроса.
Есть два способа предотвратить это:
- Установить настройку порога стоимости для параллелизма на экземпляре очень высокой, чтобы ни один последовательный план не мог быть признан достаточно дорогим для параллельного выполнения
-
Заставить всех работать в группе нагрузки Resource Governor (только Enterprise Edition [Правка 2/4/26: в Standard Edition начиная с SQL Server 2025]) с установленным
MAX_DOPв 1
Или вы можете выяснить, где используется подсказка запроса, и удалить её.
В этой статье я покажу вам простой сеанс Extended Events, который захватит информацию о том, что вызывает ожидания CXPACKET (ожидания латча ACCESS_METHOD_DATASET_PARENT не могут возникать без одновременных ожиданий CXPACKET), а затем уточню его, чтобы использовать менее дорогое событие.
Сначала я установлю свой MAXDOP в 1:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 1;
RECONFIGURE WITH OVERRIDE;
GO
SELECT [name], [value_in_use] FROM sys.configurations WHERE [name] LIKE '%degree%';
GO
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'max degree of parallelism' changed from 0 to 1. Run the RECONFIGURE statement to install.
name value_in_use
-------------------------- -------------
max degree of parallelism 1Затем я проверю наличие ожиданий CXPACKET (используя мой запрос по ожиданиям) после выполнения следующего запроса, который сканирует таблицу в 6,7 миллиона строк (базу данных SalesDB можно взять здесь):
SELECT * FROM [SalesDB].[dbo].[Sales] WHERE [Quantity] > 10000;
GO
Ожиданий CXPACKET нет.
Но если я добавлю подсказку запроса MAXDOP и затем проверю ожидания:
SELECT * FROM [SalesDB].[dbo].[Sales] WHERE [Quantity] > 10000 OPTION (MAXDOP 8);
GO
| WaitType | Wait_S | Resource_S | Signal_S | WaitCount | Percentage | AvgWait_S | AvgRes_S | AvgSig_S | Help/Info URL |
|---|---|---|---|---|---|---|---|---|---|
| LATCH_EX | 0.20 | 0.16 | 0.05 | 93 | 79.61 | 0.0022 | 0.0017 | 0.0005 | https://www.sqlskills.com/help/waits/LATCH_EX |
| CXPACKET | 0.05 | 0.05 | 0.00 | 16 | 20.00 | 0.0032 | 0.0032 | 0.0000 | https://www.sqlskills.com/help/waits/CXPACKET |
И MAXDOP экземпляра был успешно проигнорирован и переопределён.
Теперь я настрою простой сеанс Extended Events, чтобы отследить offending-код (на основе запроса отсюда). Очень важно запросить DMV sys.dm_xe_map_values, чтобы найти правильное число для использования в запросе для ожидания CXPACKET, так как эти числа часто меняются от выпуска к выпуску и даже в пакетах обновления. Например, CXPACKET был 191 в SQL Server 2014 RTM, но 190 в сборке 2014, которую я использую.
Будьте очень осторожны при запуске этого в продакшене, так как событие будет срабатывать при каждом возникающем ожидании и, вероятно, повлияет на пропускную способность вашей рабочей нагрузки, даже если оно замкнётся, если ожидание не CXPACKET. Ниже я покажу вам лучшее событие для использования.
IF EXISTS (
SELECT * FROM sys.server_event_sessions
WHERE [name] = N'InvestigateWaits')
DROP EVENT SESSION [InvestigateWaits] ON SERVER
GO
CREATE EVENT SESSION [InvestigateWaits] ON SERVER
ADD EVENT [sqlos].[wait_info]
(
ACTION (
sqlserver.client_hostname,
sqlserver.nt_username,
sqlserver.sql_text)
WHERE [wait_type] = 190 -- только CXPACKET
AND [opcode] = 1 -- только события окончания ожидания
)
ADD TARGET [package0].[ring_buffer]
WITH
(
MAX_MEMORY = 50 MB,
MAX_DISPATCH_LATENCY = 5 SECONDS)
GO
ALTER EVENT SESSION [InvestigateWaits] ON SERVER STATE = START;
GO
И затем, когда я снова выполняю оператор select, я могу посмотреть в кольцевой буфер и увидеть события. Я добавил DISTINCT, чтобы минимизировать количество строк вывода. Код:
SELECT
DISTINCT ([data1].[value] ('(./@timestamp)[1]', 'datetime')) AS [Time],
[data1].[value] ('(./action[@name="client_hostname"]/value)[1]', 'VARCHAR(MAX)') AS [Host],
[data1].[value] ('(./action[@name="nt_username"]/value)[1]', 'VARCHAR(MAX)') AS [User],
[data1].[value] ('(./action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') AS [Statement]
FROM (
SELECT CONVERT (XML, [target_data]) AS data
FROM sys.dm_xe_session_targets [xst]
INNER JOIN sys.dm_xe_sessions [xs]
ON [xst].[event_session_address] = [xs].[address]
WHERE [xs].[name] = N'InvestigateWaits') AS t
CROSS APPLY data.nodes('//event') n (data1);
GO
| Time | Host | User | Statement |
|---|---|---|---|
| 2017-02-16 00:20:16.937 | APPLECROSS | APPLECROSS\Paul | SELECT * FROM [SalesDB].[dbo].[Sales] WHERE [Quantity] > 10000 OPTION (MAXDOP 8); |
| 2017-02-16 00:20:16.987 | APPLECROSS | APPLECROSS\Paul | SELECT * FROM [SalesDB].[dbo].[Sales] WHERE [Quantity] > 10000 OPTION (MAXDOP 8); |
| 2017-02-16 00:20:16.937 | APPLECROSS | SELECT * FROM [SalesDB].[dbo].[Sales] WHERE [Quantity] > 10000 OPTION (MAXDOP 8); |
Здорово — так что это работает, но сеанс, вероятно, повлияет на производительность рабочей нагрузки, как я упоминал выше. Лучшее событие для использования — degree_of_parallelism, которое было введено в SQL Server 2012 и срабатывает только один раз на выполнение пакета, а не один раз на каждое возникающее ожидание.
Обновлённый сеанс событий:
CREATE EVENT SESSION [InvestigateWaits] ON SERVER
ADD EVENT [sqlserver].[degree_of_parallelism]
(
ACTION (
sqlserver.client_hostname,
sqlserver.nt_username,
sqlserver.sql_text)
WHERE [dop] > 1 -- параллельные планы
)
ADD TARGET [package0].[ring_buffer]
WITH
(
MAX_MEMORY = 50 MB,
MAX_DISPATCH_LATENCY = 5 SECONDS)
GO
И код для разбора XML, и пример вывода из моего запроса:
SELECT
[data1].[value] ('(./@timestamp)[1]', 'datetime') AS [Time],
[data1].[value] ('(./data[@name="dop"]/value)[1]', 'INT') AS [DOP],
[data1].[value] ('(./action[@name="client_hostname"]/value)[1]', 'VARCHAR(MAX)') AS [Host],
[data1].[value] ('(./action[@name="nt_username"]/value)[1]', 'VARCHAR(MAX)') AS [User],
[data1].[value] ('(./action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') AS [Statement]
FROM (
SELECT CONVERT (XML, [target_data]) AS data
FROM sys.dm_xe_session_targets [xst]
INNER JOIN sys.dm_xe_sessions [xs]
ON [xst].[event_session_address] = [xs].[address]
WHERE [xs].[name] = N'InvestigateWaits') AS t
CROSS APPLY data.nodes('//event') n (data1);
GO
| Time | DOP | Host | User | Statement |
|---|---|---|---|---|
| 2017-02-16 00:36:37.347 | 8 | APPLECROSS | APPLECROSS\Paul | SELECT * FROM [SalesDB].[dbo].[Sales] WHERE [Quantity] > 10000 OPTION (MAXDOP 8); |
Довольно круто, а?
Вы можете возиться с более сложным кодом, который делает такие вещи, как захват действия tsql_stack и затем его разбор, и больше информации для идентификации виновника, например, имени приложения — насколько сложно вы захотите — но теперь вы знаете базовый сеанс событий для захвата, когда используется подсказка запроса.
