SQL-Ex blog

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

ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 140;

База данных WideWorldImporters используется во всех примерах этой статьи (бэкап этой базы данных можно скачать отсюда).

Чтобы узнать уровень совместимости базы данных, используйте следующий оператор SELECT:

SELECT compatibility_level FROM sys.databases 

WHERE name = 'WideWorldImporters';

В настоящее время SQL Server 2017 поддерживает три типа адаптивной обработки запросов:

• Обратная связь по временно предоставляемому буферу памяти в пакетном режиме.


• Адаптивное соединение в пакетном режиме.


• Выполнение с чередованием.

Эти возможности доступны по умолчанию. Однако вы можете запретить или разрешить каждую из них индивидуально без изменения уровня совместимости базы данных. Рассмотрим эти возможности более детально.

Обратная связь по временно предоставляемому буферу памяти

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

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

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

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

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

SELECT * FROM sys.database_scoped_configurations;

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

Этот оператор SELECT вернет информацию о возможных установках конфигурационных параметров, показанных на рис.1.

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

Теперь выполним следующий оператор SELECT с включенным параметром Actual Execution Plan (в Management Studio меню "Запрос", команда "Включить действительный план выполнения"):

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 Атрибут выделения памяти оператора Select

Атрибут Memory Grant показывает, что для строк данных запроса требуется 78464 KB памяти. Независимо от того, сколько раз вы будете перезапускать этот запрос, общее значение Memory Grant будет тем же самым, пока план запроса сохраняется в кэше. Даже если вы получаете отличное от показанного на рисунке значение, поведение будет таким же.

Теперь проверим способность обратной связи по выделению памяти, включив этот параметр и запустив оператор 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. Это справедливо для всех подобных конфигурационных опций, разрешающих или запрещающих возможности адаптивной обработки запроса.

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

DBCC FREEPROCCACHE;

Этот метод удалит все планы запросов из кэша. Если это вас не устраивает, вы должны указать конкретный план, который вы хотите удалить.

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

При первом перезапуске оператора вы должны получить те же результаты, что и прежде, когда атрибут Memory Grant показывает 78464 KB памяти или близкое к этому значение. Однако при следующем выполнении этот показатель должен быть значительно ниже. На моей системе последующие выполнения дают значение атрибута Memory Grant 14592 KB, как видно на рис.3.

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

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

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 опять покажет значение выделяемой памяти 78464 KB.

Адаптивные соединения (Adaptive Joins)

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

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

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

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;

Для проверки установки опции, блокирующей адаптивные соединения, выполните следующий оператор SELECT:

SELECT * FROM sys.database_scoped_configurations;

На рис.4, который показывает результат выполнения оператора SELECT, видно, что опция 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, который демонстрирует сканирование индекса поколоночного хранения (columnstore index scan), сканирование некластерного индекса (nonclustered index scan) и хэш-соединение (hash join).

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

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

Тогда следующим шагом будет разрешение адаптивных соединений, которое можно дать следующим оператором ALTER DATABASE:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;

После выполнения этого оператора запустим предыдущий оператор SELECT.

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

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

Оператор Clustered Index Seek включается при необходимости для использования соединением вложенными циклами (nested loop join). Обратите внимание, что указано 0 из 24370. Это говорит о том, что эта ветвь плана не используется, вместо неё для выполнения этой операции используется хэш-соединение (hash join).

Оператор адаптивного соединения определяет, какой тип соединения используется, посредством вычисления порогового значения. Сравнение порогового значения с числом строк определяет что выполнять - hash join или же nested loop join. В данном случае пороговое значение равно 159.754, а число строк - 24459. Если число строк больше или равно пороговому значению, план запроса использует hash join. В противном случае используется nested loop join.

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

• Предполагаемый тип соединения (Estimated Join Type), который установлен в HashMatch


• Строки адаптивного порога (Adaptive Threshold Rows), который равен 159.754


• Является ли адаптивным (Is Adaptive), уcтановленный в True

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

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

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

UPDATE Sales.InvoiceLines SET Quantity = 361

WHERE InvoiceLineID = 41606;

Затем снова выполним оператор SELECT, только теперь для Quantity укажем значение 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.

Чтобы вернуть базу данных у исходное состояние, выполните запрос:

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

Выполнение с чередованием

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

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

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

CREATE 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 (включено):

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;

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

SELECT * FROM sys.database_scoped_configurations;

На рис.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 Атрибут Estimated Number of Rows оператора Table Valued Function

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

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

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF;

Снова выполним тот же оператор SELECT с тем же значением параметра (100) вызова функции. Перейдем на план выполнения и переведем курсор на оператор Table Valued Function. Теперь атрибут Estimated Number of Rows показывает значение 24459, что видно на рис.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'));

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

В зависимости от типа запроса, который вы выполняете, возможность адаптивной обработки может дать значительный прирост производительности, особенно при росте рабочей нагрузки. Пока неясно, будет ли Майкрософт развивать эти возможности в ближайшее время, но, вероятно, мы увидим некоторые улучшения. Например, Майкрософт мог бы распространить адаптивные соединения на операторы модификации данных или вывести выполнение с чередованием за пределы MSTVF. Фактически Майкрософт уже реализовал ознакомительную версию с возможностью отложенной компиляции табличных переменных (Table Variable Deferred Compilation) в рамках баз данных Azure SQL и SQL Server 2019.

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