Надеюсь, вам нравится эта серия. Я искренне верю, что она может быть очень полезной для тех, кто профессионально использует SQL Server, как это делаю я уже более 20 лет (как летит время!).
Сегодня мы поговорим о пакетном режиме для строчного хранения (Batch Mode on Rowstore) и о том, как сделать ваш запрос в 10 раз быстрее без добавления каких-либо индексов.
Что ж... в Части 3 мы представили колоночные индексы и увидели, как выполнение в пакетном режиме может кардинально улучшить аналитические запросы.
Если вы пропустили предыдущую часть, вы можете прочитать её здесь:
Но вот в чём поворот.
Начиная с SQL Server 2019, вам не всегда нужен колоночный индекс, чтобы получить производительность пакетного режима.
SQL Server может активировать пакетный режим для строчного хранения.
И иногда... как я сказал вам в начале этой статьи... ваш запрос становится в 5 или 10 раз быстрее без добавления ни одного индекса. Поэтому я предлагаю вам продолжить чтение...
Что такое пакетный режим?
Традиционно SQL Server обрабатывает запросы в строчном режиме (Row Mode). Это означает:
- Одна строка за раз
- Один вызов оператора на строку
- Высокие накладные расходы CPU для больших наборов данных
Пакетный режим (Batch Mode) меняет модель выполнения:
- Обрабатывает строки пакетами (обычно до 900 строк на пакет)
- Использует векторизованную обработку
- Уменьшает количество тактов CPU на строку
- Кардинально улучшает производительность агрегаций и соединений
Вместо миллионов вызовов операторов, SQL Server обрабатывает фрагменты строк за одну эффективную с точки зрения CPU операцию.
Строчный режим против пакетного режима – реальная разница
| Характеристика | Строчный режим | Пакетный режим |
|---|---|---|
| Единица обработки | 1 строка | До ~900 строк |
| Эффективность CPU | Ниже | Намного выше |
| Скорость агрегации | Умеренная | Очень высокая |
| Использование памяти | Стандартное | Оптимизированное |
Пакетный режим не просто быстрее. Он меняет то, как операторы выполняются внутри.
Когда SQL Server активирует пакетный режим для строчного хранения?
Пакетный режим для строчного хранения доступен начиная с:
- SQL Server 2019
- Уровень совместимости базы данных 150
Чтобы включить его:
ALTER DATABASE YourDatabase
SET COMPATIBILITY_LEVEL = 150;SQL Server может автоматически выбрать пакетный режим, если:
- Запрос является аналитическим (GROUP BY, агрегаты, соединения)
- Задействовано большое количество строк
- Оценочная стоимость оправдывает векторизацию
- Колоночный индекс не требуется.
Демонстрация – строчный режим против пакетного режима
Давайте протестируем большую агрегацию:
SELECT CustomerID,
SUM(Amount) AS TotalAmount
FROM dbo.Sales
GROUP BY CustomerID;Шаг 1 – Уровень совместимости 140 (поведение SQL Server 2017)
ALTER DATABASE YourDatabase
SET COMPATIBILITY_LEVEL = 140;План выполнения:
- Хеш-агрегация (Hash Aggregate)
- Строчный режим
- Высокое потребление CPU
Шаг 2 – Уровень совместимости 150
ALTER DATABASE YourDatabase
SET COMPATIBILITY_LEVEL = 150;План выполнения теперь показывает:
- Хеш-агрегация (пакетный режим) — Hash Aggregate (Batch Mode)
- Векторизованное выполнение
- Меньшая стоимость CPU
Сравнение производительности
| Уровень совместимости | Режим выполнения | CPU (мс) | Время выполнения (мс) |
|---|---|---|---|
| 140 | Строчный режим | 1200 | 1500 |
| 150 | Пакетный режим | 220 | 320 |
Тот же запрос. Те же индексы. Те же данные.
Совершенно иное поведение движка.
Обратная связь по выделению памяти (Memory Grant Feedback)
SQL Server 2019 также улучшает управление памятью с помощью обратной связи по выделению памяти.
Если первое выполнение:
- Переоценивает память → тратит ресурсы впустую
- Недооценивает память → приводит к сбросу данных в tempdb (spills)
SQL Server корректирует выделение памяти для последующих выполнений.
В сочетании с пакетным режимом это создаёт:
- Более стабильную производительность
- Меньше материализаций в tempdb
- Лучшую параллельность (concurrency)
Адаптивные соединения (Adaptive Joins)
Ещё одна интеллектуальная функция, представленная в современных версиях, — это адаптивные соединения.
Вместо выбора между вложенным циклом (Nested Loop) или хеш-соединением (Hash Join) на этапе компиляции, SQL Server может принять решение во время выполнения, основываясь на фактическом количестве строк.
Это снижает риск:
- Плохих оценок количества строк
- Побочных эффектов от Sniffing параметров
- Нестабильности плана
Пакетный режим чрезвычайно хорошо работает вместе с адаптивными соединениями.
Почему это важно
До SQL Server 2017 для получения пакетного режима требовался колоночный индекс.
Начиная с SQL Server 2019, вы можете получить ускорение аналитики без изменения вашей схемы.
Это означает:
- Никакого дополнительного обслуживания индексов
- Никакого дополнительного хранилища
- Никакого перепроектирования схемы
Иногда производительность заключается не в добавлении объектов, а во включении правильной модели выполнения.
Заключительные мысли
- Фильтрованный индекс уменьшает ввод-вывод.
- Индексированное представление устраняет затраты на агрегацию.
- Колоночный индекс меняет движок хранения.
- Пакетный режим для строчного хранения меняет движок выполнения.
И это совершенно иной уровень оптимизации.
В настройке производительности самые большие выгоды часто достигаются не за счёт добавления новых индексов, а за счёт понимания того, как SQL Server на самом деле выполняет ваши запросы.
Комментариев нет:
Отправить комментарий