SQL-Ex blog

Что это означает? Это значит, что операции запроса могут обрабатывать данные быстрей, более эффективно и имитировать то, что делает поколоночное хранение столь быстрым. Вместо чтения строку за строкой (режим построчного хранения), он может читать кусками, т.е. пакетами по 900 строк за раз. Влияние на производительность может быть огромным, что приводит к более эффективному использованию ЦП.

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

Откуда движок знает, когда использовать пакетный режим? Согласно документации Microsoft, процессор запросов использует эвристики и принимает решение на основании трех проверок. Начальная проверка на размеры таблиц, используемые операторы и оценки кардинальных чисел. Затем оптимизатор проверяет, если ли более дешевый план, который он может использовать. Если нет лучшего альтернативного плана, оптимизатор выберет пакетный режим. Существуют некоторые ограничения, которые будут препятствовать использованию пакетного режима, как то OLTP-таблицы в памяти (in-memory) или для любого индекса, отличного от B-Tree, или кучи на диске. Он также не будет работать на LOB-столбцах, включая разреженные столбцы и столбцы XML.

Вы можете легко распознать, когда пакетный режим применяется для выполнения запроса, по свойствам оператора. Давайте рассмотрим пример.

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

USE AdventureworksDW2016CTP3

GO

ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;

Теперь давайте выполним запрос и убедимся, что мы захватили план выполнения.

SELECT FS.[ProductKey],

       AVG([SalesAmount]),

       [UnitPrice]

FROM [dbo].[FactResellerSalesXL] FS

    JOIN dbo.DimProduct DP

        ON DP.ProductKey = FS.ProductKey

GROUP BY FS.[ProductKey],

            [UnitPrice]

Обратите внимание на сканирование таблицы (Table Scan). Наведите курсор на значок, чтобы увидеть свойства оператора; свойство Actual Execution Mode (фактический режим выполнения) имеет значение ROW (построчный), при этом обрабатывается 11669653 строки.



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

SELECT FS.[ProductKey],

       AVG([SalesAmount]),

       [UnitPrice]

FROM [dbo].[FactResellerSalesXL] FS

    JOIN dbo.DimProduct DP

        ON DP.ProductKey = FS.ProductKey

GROUP BY FS.[ProductKey],

            [UnitPrice]

OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));

Ясно видно, что оптимизатор выбрал пакетный режим (BATCH) на основе нашего хинта. Кроме того, вы можете увидеть, что запрос выполнился значительно быстрее - только 405 мс против 1,119 при использовании построчного режима. В общем, мы видели запросы, которые выигрывают от пакетного режима, выполняясь почти вдвое производительней, чем при построчном режиме и поколоночном хранении в пакетном режиме.



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

ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON;

GO

SELECT FS.[ProductKey],

       AVG([SalesAmount]),

       [UnitPrice]

FROM [dbo].[FactResellerSalesXL] FS

    JOIN dbo.DimProduct DP

        ON DP.ProductKey = FS.ProductKey

GROUP BY FS.[ProductKey],

            [UnitPrice]

Бинго! Он используется!



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