Поскольку были выпущены новые версии SQL Server, оптимизатор запросов претерпел множество улучшений. Адаптивная обработка запросов (Adaptive Query Processing), новая возможность в  SQL Server 2017, представляет собой новое направление. Этот набор функций обеспечивает некоторую гибкость для разных типов соединений, многозначных табличных функций и предоставлений памяти. В этой статье мы объясним три функции, которые составляют Adaptive Query Processing

SQL Server 2017 теперь предлагает адаптивную обработку запросов - новый набор функций, направленных на повышение производительности запросов. Адаптивная обработка запросов обрабатывает задачи, связанные с оценками мощности в планах выполнения, что приводит к лучшему распределению памяти, выбору типа соединения и вычислениям строк для многозначных табличных функций (MSTVF).

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

Адаптивные функции обработки запросов пытаются решить эти проблемы, предоставляя более точные оценки мощности при расчете планов выполнения запросов. SQL Server 2017 включает эти функции по умолчанию в базах данных, настроенных с уровнем совместимости 140 или выше.

Если база данных имеет более низкий уровень совместимости, вы можете использовать оператор ALTER DATABASE для изменения уровня. Например, следующий оператор изменяет уровень совместимости базы данных образцов WideWorldImporters на 140:

ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 140;

База данных WideWorldImporters используется для всех примеров этой статьи. Если эта база данных установлена в вашей системе, вы можете попробовать примеры без каких-либо изменений. Если вы хотите использовать другую базу данных, вы можете создавать инструкции SELECT, сопоставимые с показаниями, приведенными в примерах. Те же принципы должны применяться к любой базе данных с уровнем совместимости 140, работающим на SQL Server 2017.

Вы можете проверить уровень совместимости базы данных, выполнив следующую инструкцию SELECT, передав имя базы данных в предложении WHERE:

SELECT compatibility_level FROM sys.databases

WHERE name = 'WideWorldImporters';

Если вы запустите этот оператор SELECT после выполнения предыдущего оператора ALTER DATABASE, оператор SELECT должен вернуть значение 140.

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

• Обратная связь с памятью пакетного режима
• Адаптивное соединение пакетного режима
• Выполнение чередования

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

Обратная связь с поддержкой памяти

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

Функция обратной связи с поддержкой памяти помогает устранить эту ситуацию, пересчитав требования к памяти строки, когда сначала выполняется оператор. Если начальная оценка отключена, обновляется кешированный план. Последующие исполнения могут извлечь выгоду из новой оценки, если план запроса остается в кеше.

Лучший способ понять, как работает обратная связь с памятью, - увидеть его в действии, начиная с того, как традиционно вел себя SQL Server при оценке требований к памяти. Чтобы продемонстрировать это поведение, сначала отключите функцию обратной связи с поддержкой памяти, выполнив следующую инструкцию ALTER DATABASE:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

Оператор устанавливает для параметра конфигурации DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK значение ON, что отключает функции обратной связи с поддержкой памяти, не влияя на уровень совместимости базы данных. Чтобы убедиться, что параметр был обновлен, запустите следующую инструкцию SELECT:

SELECT * FROM sys.database_scoped_configurations;

Оператор SELECT возвращает данные о параметрах конфигурации области базы данных, как показано на рисунке 1.

Рисунок 1. Отключение обратной связи с поддержкой памяти

Шестая строка результатов включает параметр DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK. Обратите внимание, что текущее значение опции равно 1 (ON) и что значение по умолчанию равно 0 (OFF), что указывает на то, что обратная связь с поддержкой памяти включена по умолчанию (но только для баз данных с уровнем совместимости 140 или выше).

Затем запустите следующую инструкцию SELECT с включенным планом фактического выполнения:

SELECT il.StockItemID, il.Quantity, il.ExtendedPrice, iv.InvoiceDate

FROM Sales.InvoiceLines il INNER JOIN Sales.Invoices iv

 ON il.InvoiceID = iv.InvoiceID

WHERE iv.InvoiceDate BETWEEN '2013-01-01' AND '2015-12-31'

ORDER BY il.StockItemID, il.Quantity DESC;

После выполнения инструкции перейдите к плану выполнения и наведите указатель мыши на оператор Select, чтобы отобразить данные оператора, которые показаны на рисунке 2.

Рисунок 2. Атрибут Memory Grant оператора Select

Атрибут Memory Grant указывает, что для данных строки запроса требуется 78,464 КБ памяти. Независимо от того, сколько раз вы повторно запускаете инструкцию SELECT, вы должны получить тот же самый общий объем памяти, если план запроса остается кешированным. Даже если вы получаете другую сумму, чем показанная здесь, поведение должно быть одинаковым.

Имея это в виду, теперь вы можете протестировать функцию обратной связи с поддержкой памяти, повторно включив эту функцию, а затем повторно выполнив инструкцию SELECT. Чтобы снова включить эту функцию, запустите следующую инструкцию ALTER DATABASE, которая устанавливает для параметра DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK значение OFF:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Когда вы ставите параметр DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK в положение OFF, этот параметр больше не указан в таблице sys.database_scoped_configurations. Только когда вы устанавливаете опцию ON, она включена в таблицу. Это справедливо для всех параметров конфигурации, определенных для включения или отключения функций обработки адаптивных запросов.

После того, как вы снова включите функцию обратной связи с поддержкой памяти, вы должны повторно запустить пример инструкции SELECT. Однако прежде чем вы это сделаете, очистите план выполнения из кеша. (При необходимости вы должны очистить кеш между каждым примером, чтобы убедиться, что вы видите правильное поведение при тестировании этих операторов, но не делайте этого на производственном сервере. Фактически, вы никогда не должны тестировать новые функции на производственном сервере.) Один из способов очистки кеша - запустить следующую инструкцию T-SQL:

DBCC FREEPROCCACHE;

SQL Server предоставляет несколько способов очистки кеша, поэтому выбирайте, какой из них работает для вас. Операция DBCC FREEPROCCACHE представляет собой довольно простой подход, пока для всех планов запросов хороша очистка от кеша. Если это не так, вам нужно будет указать конкретный план, который вы хотите удалить.

После повторного включения функции обратной связи с поддержкой памяти и очистки кеша выполните следующую инструкцию SELECT от двух раз (это тот же оператор SELECT, что и выше):

SELECT il.StockItemID, il.Quantity, il.ExtendedPrice, iv.InvoiceDate

FROM Sales.InvoiceLines il INNER JOIN Sales.Invoices iv

 ON il.InvoiceID = iv.InvoiceID

WHERE iv.InvoiceDate BETWEEN '2013-01-01' AND '2015-12-31'

ORDER BY il.StockItemID, il.Quantity DESC;

При первом запуске этого утверждения вы должны получить те же результаты, что и раньше, с атрибутом Memory Grant, отображающим в общей сложности 78 464 КБ памяти или что-то близкое к этому. Однако, когда вы повторно запускаете утверждение, сумма должна быть намного ниже. В нашей системе последующие исполнения привели к тому, что общий объем памяти составил 14 592 КБ, как показано на рисунке 3.

Рисунок 3. Атрибут Memory Grant оператора Select

Когда мы протестировали функцию обратной связи с памятью в своей системе, мы неоднократно повторяли вышеуказанный оператор SELECT. Хотя мы в целом получили то же самое общее количество памяти, описанное здесь, в некоторых случаях мы бы получил исходную оценку или даже другое значение. По большей части, однако, функция работала как заявленная, несмотря на относительно небольшое количество несоответствий.

Вы также можете отключить функцию обратной связи с поддержкой памяти для каждого оператора, включив предложение OPTION, которое указывает подсказку DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK, как показано в следующем операторе SELECT:

SELECT il.StockItemID, il.Quantity, il.ExtendedPrice, iv.InvoiceDate

FROM Sales.InvoiceLines il INNER JOIN Sales.Invoices iv

 ON il.InvoiceID = iv.InvoiceID

WHERE iv.InvoiceDate BETWEEN '2013-01-01' AND '2015-12-31'

ORDER BY il.StockItemID, il.Quantity DESC

OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));

Атрибут Memory Grant должен еще раз показать 78,464 КБ памяти (или что-то подобное), независимо от того, как часто вы запускаете заявление, по крайней мере, до тех пор, пока план не будет восстановлен.

Адаптивные объединения

Когда оператор SELECT включает условие объединения, механизм запроса пытается определить лучший тип соединения для использования на основе оценочного количества строк. До SQL Server 2017, если в плане выполнения был выбран тип плохого соединения, мало чего можно было бы сделать, за исключением указания подсказки запроса или конкретного типа соединения.

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

Вы можете увидеть, как работает эта функция, сравнивая старый метод с новым, аналогично подходу, применяемому при тестировании функции обратной связи с поддержкой памяти. Чтобы отключить функцию адаптивного соединения, запустите следующий оператор ALTER DATABASE, установив для параметра DISABLE_BATCH_MODE_ADAPTIVE_JOINS значение ON:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;

Неудивительно, что опция DISABLE_BATCH_MODE_ADAPTIVE_JOINS специфична для адаптивных объединений. Тем не менее, все варианты обработки адаптивных запросов работают одинаково. Чтобы отключить функцию, установите ее соответствующую опцию в положение ON, а для включения этой функции установите значение OFF.

Чтобы убедиться, что параметр был установлен в положение ON, а функция отключена, вы можете запустить следующую инструкцию SELECT:

SELECT * FROM sys.database_scoped_configurations

Оператор SELECT возвращает результаты, показанные на рисунке 3, которые показывают, что параметр DISABLE_BATCH_MODE_ADAPTIVE_JOINS установлен на 1 (ON) и что состояние по умолчанию - 0 (OFF).

Рисунок 4. Отключение адаптивных соединений

Затем запустите следующую инструкцию SELECT, только на этот раз включите Live Query Statistics:

SELECT iv.InvoiceID, il.InvoiceLineID, il.StockItemID, il.Quantity

FROM Sales.Invoices iv INNER JOIN Sales.InvoiceLines il

 ON iv.InvoiceID = il.InvoiceID

WHERE il.Quantity > 100;

План выполнения должен выглядеть так, как показано на рисунке 5, который показывает сканирование индекса столбцов, некластеризованное сканирование индексов и хеш-соединение.

Рисунок 5. Выполнение неадаптивного соединения

Поскольку статистика Live Query Statistics была включена, в плане также показаны подсчеты для количества строк по сравнению с количеством оценочных строк, все из которых должны выглядеть довольно просто. Фактически, мы включили этот пример только для сравнения его с планом запроса, когда включены адаптивные объединения.

Следующий шаг - включить адаптивные объединения, выполнив следующую инструкцию ALTER DATABASE:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;

После запуска этого оператора повторно запустите предыдущий оператор SELECT (он снова показан для вашего удобства):

SELECT iv.InvoiceID, il.InvoiceLineID, il.StockItemID, il.Quantity

FROM Sales.Invoices iv INNER JOIN Sales.InvoiceLines il

 ON iv.InvoiceID = il.InvoiceID

WHERE il.Quantity > 100;

Теперь рассмотрим план выполнения. Вы найдете пару дополнений, включая оператор Clustered Index Seek и, что более важно, новый оператор Adaptive Join, как показано на рисунке 6.

Рисунок 6. Выполнение адаптивного соединения

Оператор Clustered Index Seek включен для использования объединением вложенным циклом, если это необходимо. Обратите внимание, что указано значение 0 из 24370, указывая, что эта ветка не используется, что означает, что для этой операции было выбрано хеш-соединение.

Оператор Adaptive Join определяет, какой тип соединения используется для вычисления порога, который определяет, выполнять ли хеш-соединение или объединение вложенным циклом, на основе количества строк. В этом случае этот порог составляет 159,754, а количество строк - 24,459. Если количество строк больше или равно порогу, в плане запроса используется хеш-соединение. В противном случае в плане используется объединение вложенным циклом.

Если вы наводите курсор на оператор Adaptive Join для отображения деталей, вы увидите, что они включают в себя три важных атрибута:

• Предполагаемый тип соединения, для которого установлен HashMatch
• Адаптивные пороговые строки, которые установлены на 159.754
• Является Adaptive, для которого установлено значение True

На рисунке 7 показаны детали оператора Adaptive Join после запуска оператора SELECT с включенной функцией адаптивного соединения.

Рисунок 7. Атрибуты оператора Adaptive Join

Предположим, что теперь вы используете следующую инструкцию UPDATE в таблице InvoiceLines:

UPDATE Sales.InvoiceLines SET Quantity = 361

WHERE InvoiceLineID = 41606;

Затем снова запустите предыдущий оператор SELECT, только в этот раз укажите значение количества 360 в условии WHERE:

SELECT iv.InvoiceID, il.InvoiceLineID, il.StockItemID, il.Quantity

FROM Sales.Invoices iv INNER JOIN Sales.InvoiceLines il

 ON iv.InvoiceID = il.InvoiceID

WHERE il.Quantity > 360;

На этот раз детали для оператора Adaptive Join покажут тип соединения как NestedLoops и порог как 104.24.

Если вы хотите вернуть базу данных WorldWideImporters в исходное состояние, запустите следующую инструкцию UPDATE:

UPDATE Sales.InvoiceLines SET Quantity = 360

WHERE InvoiceLineID = 41606;

Имейте в виду, что функция адаптивного соединения имеет дополнительные издержки памяти и что в настоящее время она поддерживает только инструкции SELECT (никаких инструкций по модификации данных). Кроме того, оператор должен иметь право как для хэш-соединений, так и для вложенных циклов для использования функции адаптивного соединения.

Подобно функции обратной связи с поддержкой памяти, вы можете отключить адаптивные объединения для каждого оператора, включив предложение OPTION и указав подсказку DISABLE_BATCH_MODE_ ADAPTIVE_JOINS, как показано в следующей инструкции SELECT:

SELECT iv.InvoiceID, il.InvoiceLineID, il.StockItemID, il.Quantity

FROM Sales.Invoices iv INNER JOIN Sales.InvoiceLines il

 ON iv.InvoiceID = il.InvoiceID

WHERE il.Quantity > 100

OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'));

Когда вы включаете условие OPTION, оператор SELECT будет работать так же, как в базе данных с уровнем совместимости раньше 140, но не влияя на текущий уровень совместимости.

Чередующееся выполнение

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

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

Чтобы узнать, как работает эта функция, начните с выполнения следующей инструкции CREATE FUNCTION, которая определяет очень простой MSTVF:

REATE FUNCTION dbo.GetInvoiceLines (@qty INT)

RETURNS @tbl TABLE(LineID INT, InvoiceID INT, Quantity INT, Total DECIMAL)

WITH SCHEMABINDING

AS

BEGIN

 INSERT @tbl

 SELECT InvoiceLineID, InvoiceID, Quantity, ExtendedPrice

 FROM Sales.InvoiceLines

 WHERE Quantity > @qty

 RETURN

END;

GO

Затем отключите функцию выполнения с чередованием, выполнив следующую инструкцию ALTER DATABASE, установив опцию DISABLE_INTERLEAVED_EXECUTION_TVF в положение ON:

Это так же, как вы видели в предыдущих примерах, за исключением того, что это специфично для чередующихся исполнений. Также как и раньше, чтобы убедиться, что параметр был установлен в ON, а функция отключена, вы можете запустить следующую инструкцию SELECT:

SELECT * FROM sys.database_scoped_configurations;

Оператор SELECT возвращает результаты, показанные на рисунке 8, которые показывают, что опция DISABLE_INTERLEAVED_EXECUTION_TVF установлена на 1 (ON), а по умолчанию - на 0 (OFF).

Рисунок 8. Отключение чередующегося выполнения

Затем запустите следующую инструкцию SELECT с включенным планом фактического выполнения:

SELECT il.LineID, il.Quantity, il.Total, iv.InvoiceDate

FROM dbo.GetInvoiceLines(100) il INNER JOIN Sales.Invoices iv

 ON il.InvoiceID = iv.InvoiceID

WHERE il.Total > 1000;

Оператор SELECT объединяет функцию GetInvoiceLines в таблицу Sales.Invoices, передавая в качестве значения функции значение 100. Затем перейдите к плану выполнения и наведите указатель на Table Valued Function. Подробности оператора должны показать значение 100 для атрибута  Estimated Number of Rows, как показано на рисунке 9.

Рисунок 9. Оценочное число атрибутов строк для оператора Table Valued Function

Хотя оператор Table Valued Function оценивает 100 строк, функция фактически возвращает 24 459 строк, что делает существенное отличие  между этими двумя суммами. Вы можете увидеть эту сумму, просмотрев детали для оператора Table Scan (атрибут Number of Rows) или напрямую запросив функцию.

Чтобы увидеть, как чередующееся выполнение изменяет это поведение, сначала повторно включите эту функцию, установив опцию DISABLE_INTERLEAVED_EXECUTION_TVF в положение OFF:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF;

Затем перезапустите оператор SELECT сверху, передав одно и то же значение параметра (100) при вызове функции:

SELECT il.LineID, il.Quantity, il.Total, iv.InvoiceDate

FROM dbo.GetInvoiceLines(100) il INNER JOIN Sales.Invoices iv

 ON il.InvoiceID = iv.InvoiceID

WHERE il.Total > 1000;

Наконец, перейдите к плану выполнения и наведите указатель на оператор таблицы значений. Подробности оператора должны теперь показать значение 24459 для атрибута Estimated Number of Rows, как показано на рисунке 10.

Рисунок 10. Атрибута Estimated Number of Rows для оператора Table Valued Function

Возможность вернуть более точную оценку строки для MSTVF может помочь повысить производительность запросов, особенно когда функция возвращает большое количество строк. Однако, в некоторых случаях вы можете отключить эту функцию на основе каждого заявления, как вы видели с другими адаптивными функциями обработки запросов:

SELECT il.LineID, il.Quantity, il.Total, iv.InvoiceDate

FROM dbo.GetInvoiceLines(100) il INNER JOIN Sales.Invoices iv

 ON il.InvoiceID = iv.InvoiceID

WHERE il.Total > 1000

OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'));

Когда вы включаете это условие OPTION и указываете подсказку DISABLE_INTERLEAVED_EXECUTION_TVF, оператор Table Valued Function снова показывает оценку в 100 строк.

Адаптивная обработка запросов

В зависимости от типа запросов, которые вы выполняете, возможности адаптивной обработки запросов могут значительно повысить производительность запросов, особенно по мере увеличения размера ваших рабочих нагрузок. Пока неясно, улучшит ли Microsoft эти функции в ближайшее время, но, похоже, мы увидим некоторые улучшения. Например, Microsoft может в конечном итоге расширить возможности адаптивного присоединения к операторам модификации данных или расширить возможности чередующегося выполнения за пределами MSTVF. Фактически Microsoft уже опубликовала публичный предварительный просмотр новой функции «Отложенная компиляция табличной переменной» в базе данных Azure SQL и SQL Server 2019.

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

No Comments