Что такое batch mode?
Batch mode был представлен как способ более быстрого извлечение данных из колоночных индексов. Суть заключается в том, чтобы забирать данные большим куском, а не построчно.
Batch mode может работать с объёмом до 900 строк за раз, вместо одной строки. Он уменьшает нагрузку на ваш CPU и предотвращает множественное использование метаданных.
Batch mode можно увидеть, если навести на любой элемент в плане запроса, который выполняется в этом режиме:
Чтобы batch mode появился в вашем плане, необходим колоночный индекс на таблице
В SQL Server 2016 SP1 мы наблюдаем странную ситуацию — batch mode активируется, если в запросе мы ссылаемся на любую таблицу с колоночным индексов. Однако ваш план запроса может не использовать колоночный индекс. По этой причине есть несколько способов обойти ограничение использования batch mode
Способ 1 (бессмысленный фильтр по колоночному индексу)
Способ состоит в том, чтобы создать фильтрованный некластерный колоночный индекс, который не будет содержать ни одной строки. Например:
|
1 2 3 4 5 |
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 появился в вашем плане, вы можете сослаться на таблицу с колоночным индексов в любом месте запроса и указать бессмысленный предикат:
|
1 2 3 4 5 6 |
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 написал статью об этом (англ)
|
1 |
ALTER DATABASE [ContosoRetailDW] SET COMPATIBILITY_LEVEL = 130 |
Если во время тестирования данный способ не даёт 100% результат, то вам не следует его использовать.
Заключение
SQL Server может самостоятельно использовать batch mode с колоночными индексами и далеко не всегда требуется ему в этом помогать. Но в тех случаях, когда это необходимо, мы можем использовать трюки, описанные в этой статье
По мотивам:
Batch Mode Hacks for Rowstore Queries in SQL Server
