Что такое batch mode?
Batch mode был представлен как способ более быстрого извлечение данных из колоночных индексов. Суть заключается в том, чтобы забирать данные большим куском, а не построчно.
Batch mode может работать с объёмом до 900 строк за раз, вместо одной строки. Он уменьшает нагрузку на ваш CPU и предотвращает множественное использование метаданных.
Batch mode можно увидеть, если навести на любой элемент в плане запроса, который выполняется в этом режиме:
Чтобы batch mode появился в вашем плане, необходим колоночный индекс на таблице
В SQL Server 2016 SP1 мы наблюдаем странную ситуацию — batch mode активируется, если в запросе мы ссылаемся на любую таблицу с колоночным индексов. Однако ваш план запроса может не использовать колоночный индекс. По этой причине есть несколько способов обойти ограничение использования batch mode
Способ 1 (бессмысленный фильтр по колоночному индексу)
Способ состоит в том, чтобы создать фильтрованный некластерный колоночный индекс, который не будет содержать ни одной строки. Например:
CREATE NONCLUSTERED COLUMNSTORE INDEX nccx_agg_FirstNameByYearState ON agg.FirstNameByYearState (FirstNameId) WHERE FirstNameId = -1 and FirstNameId = -2; GO
По данному фильтру ни одна строка не попадёт в колоночный индекс.
Данный способ работает только на SQL Server 2016+, так как только с этой версии мы получили фильтрованный некластерный колоночный индекс.
Способ 2 (поддельный LEFT JOIN)
Чтобы batch mode появился в вашем плане, вы можете сослаться на таблицу с колоночным индексов в любом месте запроса и указать бессмысленный предикат:
CREATE TABLE dbo.hack (i int identity); GO CREATE NONCLUSTERED COLUMNSTORE INDEX nccx_hack on dbo.hack(i); GO /* and then in the query you're hacking... */ LEFT JOIN dbo.hack on 1=0
Оптимизатор отбросит условие «ON 1=0», так как оно бессмысленно. Обратите внимание, что вы можете заменить таблицу dbo.hack на временную таблицу.
Способ требует изменения кода, но не требует SQL Server 2016+.
Способ 3 (изменение уровня совместимости, не рекомендуется)
Первое что хотелось бы сказать — изменение уровня совместимости на даёт гарантий появления batch mode в ваших запросах, вам всё равно необходимо иметь колоночный индекс. К тому же это решение крайне спорное и должно быть хорошо протестировано.
Суть метода заключается в том, что даже с колоночным индексом, SQL Server не всегда может построить план выполнения на уровне совместимости 130, поэтому, иногда, необходимо понизить уровень совместимости до 120. Niko Neugebauer написал статью об этом (англ)
ALTER DATABASE [ContosoRetailDW] SET COMPATIBILITY_LEVEL = 130
Если во время тестирования данный способ не даёт 100% результат, то вам не следует его использовать.
Заключение
SQL Server может самостоятельно использовать batch mode с колоночными индексами и далеко не всегда требуется ему в этом помогать. Но в тех случаях, когда это необходимо, мы можем использовать трюки, описанные в этой статье
По мотивам:
Batch Mode Hacks for Rowstore Queries in SQL Server
