Как удалось убрал 32 секунды из запроса без добавления индекса

Автор: Luca Biondi, I Removed 32 Seconds From This SQL Server Query ...Without Adding an Index! Part 7

Это не теория.
Это не лабораторная настройка.
Это произошло с реальной рабочей нагрузкой.

Исходное время выполнения: 42 секунды.
После оптимизации: 9.8 секунды.

Никакого нового индекса.
Никакого изменения схемы.
Никакого обновления оборудования.

Просто понимание того, как работает движок.

Приятного чтения — давайте заставим SQL Server летать.

Продолжение серии статей, вот предыдущая: Всё ещё о Batch Mode… Параметры совместимости и опции запросов (Часть 6)

Проблема

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

Среда:

• SQL Server 2019
• Уровень совместимости 150
• Таблица со строчным хранением (Rowstore)
• Нет колоночного индекса

Запрос:

SELECT CustomerID,
       SUM(Amount) AS TotalAmount
FROM SalesBig
GROUP BY CustomerID;

Просто. Чисто. Ничего экзотического.

И всё же мучительно медленно.

Метрики "До"

SET STATISTICS IO, TIME ON;

• Время выполнения (Elapsed Time): 42 312 мс
• Время CPU (CPU Time): 38 774 мс
• Логические чтения (Logical Reads): 18+ миллионов
• Оператор: Hash Match (строчный режим)

План выполнения показывал:

• Параллельное сканирование
• Агрегация в строчном режиме
• Высокую нагрузку на CPU

Очевидно, узким местом был процессор.

Понимание

Набор данных был большим.
Агрегация была тяжёлой.
Уровень совместимости был 150.

Пакетный режим для строчного хранения (Batch Mode on Rowstore) был доступен — но SQL Server не выбрал его.

Оптимизатор принял решение на основе стоимости. Поэтому я протестировал альтернативу.

Изменение

SELECT CustomerID,
       SUM(Amount) AS TotalAmount
FROM SalesBig
GROUP BY CustomerID
OPTION (USE HINT('ForceBatchMode'));

План выполнения изменился мгновенно:

• Hash Match (пакетный режим)
• Векторизованная обработка
• Снижение количества тактов CPU на строку

Метрики "После"

• Время выполнения (Elapsed Time): 9 821 мс
• Время CPU (CPU Time): 8 104 мс
• Логические чтения (Logical Reads): примерно те же

32 секунды убрано.
Выполнение в 4 раза быстрее.
То же оборудование.
Та же таблица.

Почему это сработало

• Большой набор данных → идеально для векторизованного выполнения
• Тяжёлая агрегация → преимущество пакетного режима
• Достаточное выделение памяти → нет материализации в tempdb
• Параллелизм + пакетный режим → значительное снижение нагрузки на CPU

Пакетный режим обрабатывает данные фрагментами, а не по одной строке за раз. Это кардинально сокращает количество инструкций CPU.

Что я проверил, прежде чем назвать это победой

• Нет материализации в tempdb
• Нет проблем с памятью
• Стабильное время выполнения при повторных запусках
• Снижение CPU подтверждено через DMV

SELECT total_worker_time,
       total_elapsed_time,
       execution_count
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC;

Важное предупреждение

Принудительное включение пакетного режима НЕ ВСЕГДА улучшает производительность.

Если:

• Количество строк невелико
• Запрос представляет собой OLTP-поиск
• Выделение памяти недостаточно
• Форма плана изменяется негативно

Тогда это может дать обратный эффект.

Реальный урок

Настройка производительности — это не слепое добавление индексов.

Это понимание того, как на самом деле работает движок.

Оптимизатор принимает решения на основе стоимости. Иногда тестирование альтернативных стратегий открывает скрытый потенциал производительности.

Заключительные выводы

• Сначала измеряйте.
• Понимайте планы выполнения.
• Проверяйте уровень совместимости.
• Тестируйте пакетный режим осторожно.
• Никогда не предполагайте — всегда проводите сравнительное тестирование.

32 секунды убрано.
Ни одного индекса не добавлено.
Никаких изменений схемы.