Адаптивные соединения по строкам

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

Версия SQL Server 2017 CTP 2.0 дополнена поддержкой адаптивной обработки запросов. Традиционно оптимизатор запросов обрабатывает все варианты плана перед выполнением запроса, и SQL Server не мог этого изменить во время выполнения. Благодаря адаптивной обработке запросов SQL Server может динамически адаптировать варианты оптимизации к условиям выполнения, в частности к неверным оценкам кратности.

В первую группу улучшений входят обратная связь для адаптивного предоставления памяти в пакетном режиме (https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/29/introducing-batch-mode-adaptive-memory-grant-feedback/), адаптивное соединение в пакетном режиме (https://blogs.msdn.microsoft.com/sqlserverstorageengine/

2017/04/19/introducing-batch-mode-adaptive-joins/) и выполнение с чередованием для многооператорных функций, возвращающих табличное значение (https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-interleaved-execution-for-multi-statement-table-valued-functions/). Все они прекрасно описаны специалистом Microsoft Джо Сэком.

Зависимые от параметров запросы соединения

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

Чтобы продемонстрировать это, я создам хранимую процедуру в тестовой базе данных PerformanceV3. Вы можете загрузить исходный код, чтобы создать тестовую базу данных, по адресу: http://tsql.solidq.com/SampleDatabases/PerformanceV3.zip.

Я использую зависимый от параметров запрос в своей хранимой процедуре, он приведен в листинге 1.

Пользователь передает префикс для имени клиента и диапазон даты в качестве входных данных и получает заказы, размещенные клиентами с указанным префиксом в имени компании в течение указанного периода.

Исходя из того, что вы не намерены использовать индекс хранилища столбцов для этих таблиц, подумайте, какими будут оптимальные индексы rowstore для данного запроса. Оптимальный индекс для Customers — индекс с ключом custname (для поддержки фильтра) и включенным столбцом custid (для охвата). Выбор рекомендуемой индексации для Orders зависит от числа подходящих клиентов. Если их мало, оптимальным будет алгоритм с вложенными циклами. Индекс с custid и orderdate формирует список ключей индекса (для поддержки предикатов соединения и фильтра) и включение orderid, empid, shipperid (для охвата). При большом числе подходящих заказчиков идеальным будет алгоритм соответствия хеша Hash Match. Индекс с ключом orderdate (поддержка фильтра диапазона дат) и включение остальных столбцов из Orders (для охвата).

Таблица Orders уже имеет кластеризованный индекс с ключом orderdate. Выполните программный код листинга 2, чтобы создать оставшиеся рекомендуемые индексы для поддержки оптимальных алгоритмов вложенных циклов и Hash Match, в зависимости от кратности фильтра для Customers.

Выполните программный код листинга 3, чтобы создать процедуру GetOrders с запросом, указанным в листинге 1.

Обратите внимание: я добавил конкретный идентификатор GUID к запросу для целей диагностики, чтобы легко отслеживать план в кэше с использованием запроса, показанного в листинге 4.

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

SET STATISTICS IO, TIME ON;

Запустите приведенный ниже программный код, чтобы выполнить процедуру первый раз (выполнение 1) с высокоселективным префиксом customer:

EXEC dbo.GetOrders
   @custprefix = N'Cust [_]1000',
   @fromdate = '20140101',
   @todate = '20140430';

План для первого выполнения показан на рисунке 1.

Рисунок 1. План для выполнения 1

Цель четырех операторов в верхней правой части плана — получить подходящих клиентов из таблицы Customers для использования в качестве внешнего входа соединения с Orders. Операторы Constant Scan и Compute Scalar преобразуют шаблон LIKE в два разделителя диапазона, а затем средний верхний оператор Nested Loops применяет поиск плюс просмотр диапазона, чтобы получить подходящих клиентов из охватывающего индекса для таблицы Customers. Обратите внимание, что предполагаемое количество строк customer очень мало (2), и аналогично мало действительное количество (11). При таком небольшом предполагаемом количестве оптимальный алгоритм соединения — алгоритм цикла, что видно из выбора оптимизатора (верхний левый оператор Nested Loops).

Я получил следующую статистику для ввода-вывода и процессора при первом выполнении на моем ноутбуке: число логических операций чтения 35, время использования процессора 0 мс.

Теперь этот план запроса кэширован.

Запустите следующий программный код, чтобы выполнить процедуру во второй раз (выполнение 2) с префиксом customer, что приводит к возврату всех 20 000 существующих клиентов:

EXEC dbo.GetOrders
   @custprefix = N’Cust [_]’,
   @fromdate = ‘20140101’,
   @todate = '20140430';

План для второго выполнения показан на рисунке 2.

Рисунок 2. План для выполнения 2

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

Вы можете убедиться, что повторное использование плана выполнено, с помощью запроса из листинга 5.

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

Экран 1. Результаты исполнения кода листинга

Обратите внимание, что значение usecounts равно 2. Это свидетельствует о повторном использовании плана.

Конечно, вы могли столкнуться с противоположной ситуацией, если первое выполнение было с неселективным вводом, а последующее выполнение — с селективным. Чтобы продемонстрировать это, сначала выполните программный код из листинга 6, чтобы освободить кэшированный план (замените дескриптор плана в моем коде на собственный).

Убедитесь, что в кэше нет плана для нашего запроса (листинг 7). Вы должны получить пустой вывод (экран 2).

Экран 2. Результаты исполнения кода листинга 7

Выполните процедуру в третий раз с неселективным префиксом клиента:

EXEC dbo.GetOrders
   @custprefix = N’Cust [_]’,
   @fromdate = '20140101',
   @todate = '20140430';

Мой план для выполнения 3 показан на рисунке 3.

Рисунок 3. План для выполнения 3

На этот раз оптимизатор верно выбирает алгоритм хеш-соединения. Также обратите внимание, что, поскольку оценка кратности для Customers была для 20 000 строк (все клиенты), оптимизатор выполнил не поиск по индексу для Customers, а просмотр. Я получил следующую статистику производительности для выполнения 3: число логических операций чтения 2163, время использования процессора 141 мс.

Теперь план для хеш-соединения кэширован.

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

EXEC dbo.GetOrders
   @custprefix = N'Cust [_]1000',
   @fromdate = '20140101',
   @todate = '20140430';

На рисунке 4 показан мой план для выполнения 4.

Рисунок 4. План для выполнения 4

План использован повторно, что привело к снижению производительности. Я получил следующую статистику производительности для этого выполнения: число логических операций чтения 2163, время использования процессора 63 мс.

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

Код листинга 8 сформировал на моем компьютере результат, показанный на экране 3.

Экран 3. Результаты исполнения кода листинга 8

Используйте программный код листинга 9, чтобы освободить кэшированный план (и опять же замените дескриптор плана на собственный).

Убедитесь, что в данный момент в кэше нет плана для этого запроса (листинг 10).

Данный запрос должен сформировать пустой вывод (экран 4).

Экран 4. Результаты исполнения кода листинга 10

Адаптивное соединение по строковому хранилищу

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

Какие же условия должны быть выполнены, чтобы SQL Server обратился к использованию адаптивного соединения? Джо Сэк подробно отвечает на этот вопрос в своей статье по адресу: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-batch-mode-adaptive-joins/. В целом условия следующие.

• Уровень совместимости базы данных — 140.
• Соединение может быть выполнено как индексированным вложенным циклом соединения, так и физическим алгоритмом хеш-соединения.
• Хеш-соединение использует пакетный режим — через присутствие индекса columnstore в запросе в целом или прямую ссылку соединения на таблицу с индексом columnstore.
• Сформированные альтернативные решения вложенного цикла соединения и хеш-соединения имеют одинаковый первый дочерний элемент (внешняя ссылка).

Обратите внимание на третий элемент в списке Джо, указывающий на необходимость пакетного режима для хранилища столбцов. На сайте в разделе вопросов и ответов Джо также рассматривает возможность адаптивных соединений с включением построчного режима: «Будет ли расширен охват адаптивных соединений пакетного режима, чтобы включить построчный режим? Первая версия обеспечивает выполнение в пакетном режиме, однако в будущем мы рассматриваем возможность реализации и построчного режима».

Поэтому формально адаптивное соединение первоначально поддерживается только по хранилищу столбцов. Но я хотел бы напомнить о приеме, описанном в моей статье об операторе оконного агрегирования (Window Aggregate) пакетного режима («Агрегатный оконный оператор пакетного режима в SQL Server 2016. Часть 1», опубликована в Windows IT Pro/RE № 9 за 2016 год), где было показано, как включить пакетную обработку по строковому хранилищу, создав фиктивный фильтрованный пустой индекс хранилища столбцов. Тот же прием поможет активировать использование адаптивного соединения по строковому хранилищу, уже реализованное в версии SQL Server 2017 CTP 2.0. Дело в том, что пока индекс хранилища столбцов присутствует для одной из таблиц, участвующих в запросе, оптимизатор может использовать адаптивные соединения, даже если в действительности он не задействует индекс хранилища столбцов в плане. Учитывая это, выполните программный код из листинга 11, чтобы создать такой индекс для таблицы Orders и задействовать адаптивные соединения с нашей процедурой.

Теперь запустите процедуру в пятый раз с селективным входным префиксом клиента:

EXEC dbo.GetOrders
   @custprefix = N'Cust [_]1000',
   @fromdate = '20140101',
   @todate = '20140430';

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

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

Обратите внимание на новый оператор Adaptive Join. Внешний вход соединения такой же, как показанный ранее на рисунке 1, — соответствующие строки клиентов из таблицы Customers. Средняя ветвь плана представляет внутренний ввод соединения в случае, если выбран хеш-алгоритм. Нижняя ветвь плана представляет внутренний ввод соединения в случае, если выбран алгоритм цикла.

Взгляните на свойство Adaptive Threshold Rows, которое на основе оценок стоимости для нашего запроса определяет 932.911 строк в качестве порога для выбора используемого алгоритма соединения. Если действительное число строк из внешнего ввода больше или равно этому значению, то оператор использует хеш-алгоритм. Обратите внимание, что в нашем случае действительное число строк 11, поэтому внутренний вход для хеш-соединения не активируется вовсе (число выполнений равно 0). В нашем случае оператор переключается на алгоритм цикла, активируя внутренний ввод для цикла соединения 11 раз, поскольку существует 11 внешних строк. Как вы можете видеть, на основе начальной оценки кратности свойство Estimated Join Type — Nested Loops. В настоящее время свойство Actual Join Type — AdaptiveJoin, но задача состоит в том, чтобы показать верный тип соединения, выбранный в более поздней сборке после CTP 2.0.

Я получил следующую статистику для ввода-вывода и процессора для этого выполнения: число логических операций чтения 35, время использования процессора 0 мс. Этот план сохраняется в кэше.

Выполните процедуру в шестой раз с использованием неселективного ввода:

EXEC dbo.GetOrders
   @custprefix = N'Cust [_]',
   @fromdate = '20140101',
   @todate = '20140430';

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

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

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

Я получил следующую статистику для ввода-вывода и процессора для этого выполнения: число логических операций чтения 2163, время использования процессора 203 мс.

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

Я получил следующий вывод на своем компьютере: значение usecounts равно 2, это говорит о том, что план был использован повторно (экран 5).

Экран 5. Результаты исполнения кода листинга 12

Используйте программный код листинга 13, чтобы освободить кэшированный план (заменив дескриптор плана на собственный).

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

Этот запрос должен возвратить пустой набор.

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

EXEC dbo.GetOrders
   @custprefix = N'Cust [_]',
   @fromdate = '20140101',
   @todate = '20140430';

На рисунке 7 показан план, полученный для этого выполнения.

Рисунок 7. План для выполнения 7

Оператор Adaptive Join выбирает алгоритм хеш-соединения. Но обратите внимание, что, поскольку предполагаемое количество строк из Customers — 20 000, что соответствует общему числу клиентов в таблице, оптимизатор просматривает индекс по клиентам, вместо того чтобы использовать поиск и просмотр диапазона. Теперь этот план сохраняется в кэше.

Запустите процедуру в восьмой раз, передав селективный ввод:

EXEC dbo.GetOrders
   @custprefix = N'Cust [_]1000',
   @fromdate = '20140101',
   @todate = '20140430';

На рисунке 8 показан план, полученный для этого выполнения.

Рисунок 8. План для выполнения 8

Кэшированный план используется повторно. Оператор Adaptive Join верно выбирает алгоритм цикла. Однако, вследствие повторного использования плана, он по-прежнему просматривает индекс по Customers, хотя поиск и просмотр диапазона явно больше подходит для нашего селективного ввода. Вы можете попробовать устранить этот недостаток, например добавив указание FORCESEEK для Customers, что будет хорошим вариантом как для селективных, так и для неселективных входов, поскольку рассматриваемый индекс — охватывающий. Дело в том, что, помимо конкретной проблемы, решаемой адаптивным соединением и другими усовершенствованиями в адаптивной обработке запросов, вам потребуется устранять прочие проблемы производительности зависимых от параметров запросов точно так же, как до сих пор.

Затем выполните программный код из листинга 15, чтобы очистить объекты, созданные для примеров в данной статье.

Итак, мы разобрали вопрос активации адаптивного соединения по строковому хранилищу в CTP-версии SQL Server 2017 2.0. Вероятно, в будущих версиях SQL Server уже не потребуется использовать какие-либо хитрости. Они будут естественным образом поддерживать адаптивное соединение по строковому хранилищу, а также пакетную обработку по строковому хранилищу.

Адаптивная обработка запросов — настолько удачное дополнение к SQL Server, что производительность наших запросов несомненно повысится. Я надеюсь, что первая очередь усовершенствований в адаптивной обработке запросов лишь верхушка айсберга, и в дальнейшем они будут существенно расширены.

SELECT C.custid, C.custname, O.orderid, O.empid, O.shipperid, O.orderdate
FROM dbo.Customers AS C
  INNER JOIN dbo.Orders AS O
    ON O.custid = C.custid
WHERE C.custname LIKE @custprefix + N'%'
  AND O.orderdate BETWEEN @fromdate AND @todate;

SET NOCOUNT ON;
USE PerformanceV3; -- http://tsql.solidq.com/SampleDatabases/PerformanceV3.zip

DROP INDEX IF EXISTS idx_nc_cn_i_cid ON dbo.Customers;
DROP INDEX IF EXISTS idx_nc_cid_od_i_oid_eid_sid ON dbo.Orders;
DROP INDEX IF EXISTS idx_yesplease ON dbo.Orders;

CREATE INDEX idx_nc_cn_i_cid
  ON dbo.Customers(custname) INCLUDE(custid);

CREATE INDEX idx_nc_cid_od_i_oid_eid_sid
  ON dbo.Orders(custid, orderdate)
  INCLUDE(orderid, empid, shipperid);

CREATE OR ALTER PROC dbo.GetOrders
  @custprefix NVARCHAR(200) = N'',
  @fromdate AS DATE = '19000101',
  @todate AS DATE = '99991231'
AS

SET NOCOUNT ON;

SELECT C.custid, C.custname, O.orderid, O.empid, O.shipperid, O.orderdate
FROM dbo.Customers AS C
  INNER JOIN dbo.Orders AS O
    ON O.custid = C.custid
WHERE C.custname LIKE @custprefix + N'%'
  AND O.orderdate BETWEEN @fromdate AND @todate
OPTION (LABEL = 'F1352E2F-866A-4A10-B660-2875D18B05D8');
GO

SELECT CP.usecounts, CP.plan_handle, ST.text
FROM sys.dm_exec_cached_plans AS CP
  CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
WHERE ST.text LIKE '%F1352E2F-866A-4A10-' + 'B660-2875D18B05D8%';

SELECT CP.usecounts, CP.plan_handle, ST.text
FROM sys.dm_exec_cached_plans AS CP
  CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
WHERE ST.text LIKE '%F1352E2F-866A-4A10-' + 'B660-2875D18B05D8%';

DBCC FREEPROCCACHE ( 0x05000600B982EE5A304AC158000200000100000000000000000
   0000000000000000000000000000000000000 );

SELECT CP.usecounts, CP.plan_handle, ST.text
FROM sys.dm_exec_cached_plans AS CP
  CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
WHERE ST.text LIKE ‘%F1352E2F-866A-4A10-’ + ‘B660-2875D18B05D8%’;

SELECT CP.usecounts, CP.plan_handle, ST.text
FROM sys.dm_exec_cached_plans AS CP
  CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
WHERE ST.text LIKE ‘%F1352E2F-866A-4A10-’ + ‘B660-2875D18B05D8%’;

DBCC FREEPROCCACHE ( 0x05000600B982EE5A304AC15800020000010000000000000000000
   00000000000000000000000000000000000 );

SELECT CP.usecounts, CP.plan_handle, ST.text
FROM sys.dm_exec_cached_plans AS CP
  CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
WHERE ST.text LIKE '%F1352E2F-866A-4A10-' + 'B660-2875D18B05D8%';

CREATE NONCLUSTERED COLUMNSTORE INDEX idx_yesplease
  ON dbo.Orders(orderid) WHERE orderid = -1 AND orderid = -2;

SELECT CP.usecounts, CP.plan_handle, ST.text
FROM sys.dm_exec_cached_plans AS CP
  CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
WHERE ST.text LIKE '%F1352E2F-866A-4A10-' + 'B660-2875D18B05D8%';

DBCC FREEPROCCACHE ( 0x05000600B982EE5A304AC15800020000010000000000000000000
   00000000000000000000000000000000000 );

SELECT CP.usecounts, CP.plan_handle, ST.text
FROM sys.dm_exec_cached_plans AS CP
  CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
WHERE ST.text LIKE '%F1352E2F-866A-4A10-' + 'B660-2875D18B05D8%';

DROP INDEX IF EXISTS idx_nc_cn_i_cid ON dbo.Customers;
DROP INDEX IF EXISTS idx_nc_cid_od_i_oid_eid_sid ON dbo.Orders;
DROP INDEX IF EXISTS idx_yesplease ON dbo.Orders;
DROP PROC IF EXISTS dbo.GetOrders;