Всё ещё о Batch Mode… Параметры совместимости и опции запросов (Часть 6)

Автор: Luca Biondi, Still on Batch Mode… – Part 6: Compatibility & Query Settings That Influence Execution

Назад к серии: Как указать Batch Mode для Rowstore, если его нет в плане (Часть 5)

Мы всё ещё говорим о пакетном режиме, потому что понимание того, когда он работает, так же важно, как и знание того, как его принудительно включить.

Сегодня мы углубимся: мы рассмотрим уровни совместимости, выделение памяти, оценку количества строк и флаги трассировки.

Приятного чтения, и пусть ваш CPU остаётся холодным при тяжёлых агрегатах!

Требования к совместимости

Пакетный режим для строчного хранения требует одновременно:

• ✔ SQL Server 2019 или новее
• ✔ Уровень совместимости базы данных ≥ 150

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

Проверка уровня совместимости

SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

Изменение уровня совместимости

ALTER DATABASE CURRENT
SET COMPATIBILITY_LEVEL = 150;
GO

Всегда тестируйте перед обновлением — поведение оптимизатора меняется при смене уровней совместимости.

Глубокое погружение: настройки запросов, влияющие на пакетный режим

A) Выделение памяти (Memory Grants)

Пакетный режим сильно зависит от корректного выделения памяти. Если оптимизатор оценивает слишком мало памяти, он может избегать пакетного режима.

Хеш-соединения и агрегаты в пакетном режиме чувствительны к доступности памяти.

-- Проверка информации о выделении памяти в фактическом плане выполнения
SET STATISTICS XML ON;

SELECT CustomerID, SUM(Amount)
FROM Sales
GROUP BY CustomerID;

Ищите:

• MemoryGrantInfo в XML плане выполнения
• Материализация в tempdb (spills)
• Чрезмерно выделенную память

Недооценённая память → материализация → преимущество пакетного режима снижается.

B) Оценка количества строк (Cardinality Estimation)

Решения о применении пакетного режима зависят от оценок количества строк.

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

Уровень совместимости 150 также включает новое поведение оценки количества строк.

Вы можете сравнить поведение оценки, используя:

-- Принудительное использование старого CE
OPTION (QUERYTRACEON 9481);

-- Принудительное использование нового CE
OPTION (QUERYTRACEON 2312);

Будьте осторожны с флагами трассировки — используйте их только для тестирования или устранения неполадок.

C) Флаги трассировки и поведение движка

Определённые флаги трассировки и подсказки влияют на поведение оптимизатора и возможность использования пакетного режима.

• QUERYTRACEON 9347 (сценарии тестирования, связанные с пакетным режимом)
• QUERYTRACEON 9453 (диагностическое поведение)
• USE HINT('ForceBatchMode')

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

Принудительное включение пакетного режима не гарантирует лучшей производительности.

Всегда сравнивайте:

• Время выполнения
• Время CPU
• Логические чтения

Когда пакетный режим может не появиться

• Низкие оценочные количества строк
• Маленькие таблицы
• Недостаточное выделение памяти
• Неподдерживаемые операторы в плане
• Неправильный уровень совместимости

Пакетный режим — это решение, основанное на стоимости, а не гарантированная функция.

...Как всегда, мы проводим сравнительное тестирование, чтобы вы тоже могли это сделать!

Теория — это хорошо. Планы выполнения — лучше. Но сравнительное тестирование (benchmarking) — это истина.

Давайте сравним строчный режим и пакетный режим в контролируемых условиях.

Шаг 1 – Подготовка большого набора данных

-- Пример генератора большого набора данных
SELECT TOP (5000000)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID,
    ABS(CHECKSUM(NEWID())) % 1000 AS CustomerID,
    ABS(CHECKSUM(NEWID())) % 500 AS ProductID,
    ABS(CHECKSUM(NEWID())) % 1000 AS Amount
INTO SalesBig
FROM sys.objects a
CROSS JOIN sys.objects b;

Убедитесь, что уровень совместимости установлен на 150.

Шаг 2 – Сравнительный тест строчного режима

SET STATISTICS IO, TIME ON;

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

Запишите:

• Время CPU
• Время выполнения
• Логические чтения
• Режим оператора в плане выполнения

Шаг 3 – Сравнительный тест с принудительным пакетным режимом

SET STATISTICS IO, TIME ON;

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

Снова сравните метрики.

На больших агрегациях снижение нагрузки на CPU может быть значительным.

Шаг 4 – Очистка кэша (опциональный контролируемый тест)

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

⚠ Никогда не выполняйте это в производственной среде.

Шаг 5 – Анализ статистики выполнения через DMV

SELECT 
    qs.total_worker_time,
    qs.total_elapsed_time,
    qs.total_logical_reads,
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset/2,
    (CASE 
        WHEN qs.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
        ELSE qs.statement_end_offset 
     END - qs.statement_start_offset)/2) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_worker_time DESC;

Это даст вам реальные агрегированные данные о производительности.

Что вы должны увидеть

• Более низкое использование CPU в пакетном режиме
• Те же логические чтения (обычно)
• Лучшую производительность на больших сканированиях и агрегациях
• Минимальную выгоду на маленьких наборах данных

Если количество строк не велико, накладные расходы пакетного режима могут свести на нет его преимущества.

Заключительная мысль

Пакетный режим — это не про принудительное включение функций.

Это про измерение, сравнение и понимание.

Настройка производительности без сравнительного тестирования — это угадывание. Использование пакетного режима без понимания использования памяти и оценки количества строк — это игра в азартные игры.