19.11.26

Когда производительность меняется без изменения кода: прежде чем переписывать запрос, изучите фактический план

Автор: Aaron Bertrand, When SQL Performance Changes Without a Code Change: Read the Actual Execution Plan Before Tuning

Ваш запрос возвращает 800 строк, но, чтобы получить их, ядро СУБД обработало 108 000 строк. Возможно, именно в этой разнице и кроется вся проблема с производительностью.

«Раньше этот запрос выполнялся быстро, а мы ничего не меняли» — одну из самых распространённых подобных фраз мы слышим при разборе проблем производительности. Часто первая реакция — обновить статистику, добавить индекс или увеличить вычислительные ресурсы. Всё это действительно может помочь, но пока это лишь предположения. Гораздо правильнее начать с другого вопроса:

Какой оператор обработал значительно больше строк, чем ожидалось, и почему это произошло?

Ответ на этот вопрос даёт фактический план выполнения.

Почему один и тот же запрос SQL может начать работать медленнее

Неизменность кода вовсе не означает, что всё остальное осталось прежним. Вот наиболее распространённые причины, по которым запрос может замедлиться сам по себе:

  • Рост объёма данных: если историческая таблица удвоилась в размере, каждое соединение с ней становится более затратным.
  • Изменение плана исполнения: новые статистические сведения, повторная компиляция или другое значение параметра могут привести к тому, что оптимизатор выберет иную структуру плана.
  • Изменение распределения данных: например, если на один ключ теперь приходится больше версий записей, чем раньше.

Текст запроса остаётся прежним, но фактический порядок и объём выполняемой ядром СУБД работы могут измениться.

Предполагаемый и фактический планы исполнения

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

Чтобы получить фактический план:

  • В SSMS: перед выполнением запроса выберите пункт Include Actual Execution Plan (Ctrl+M).
  • В T-SQL: перед запросом выполните команду SET STATISTICS XML ON.
  • Для получения последнего фактического плана выполнения кэшированного запроса: включите LAST_QUERY_PLAN_STATS и обратитесь к представлению sys.dm_exec_query_plan_stats. Обе возможности поддерживаются и в Azure SQL.

Получив план, изучайте его справа налево. Для каждого оператора сравнивайте значения Estimated Number of Rows и Actual Number of Rows. Обычно начинать следует с первого оператора, на котором эти показатели резко расходятся.

Небольшой пример, который обходится слишком дорого

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

CREATE TABLE dbo.Entity        (EntityId int PRIMARY KEY, Status varchar(10));
CREATE TABLE dbo.PriceHistory  (EntityId int, ValidFrom date, Price decimal(10,2));
CREATE TABLE dbo.RegionHistory (EntityId int, ValidFrom date, Region varchar(10));

-- 1,000 entities, 800 of them Active
INSERT dbo.Entity
SELECT value, CASE WHEN value % 5 = 0 THEN 'Closed' ELSE 'Active' END
FROM GENERATE_SERIES(1, 1000);

-- 18 price versions per entity
INSERT dbo.PriceHistory
SELECT e.EntityId, DATEADD(MONTH, v.value, '2024-01-01'), 100 + v.value
FROM dbo.Entity AS e CROSS JOIN GENERATE_SERIES(1, 18) AS v;

-- 6 region versions per entity
INSERT dbo.RegionHistory
SELECT e.EntityId, DATEADD(MONTH, v.value * 3, '2024-01-01'), CONCAT('R', v.value)
FROM dbo.Entity AS e CROSS JOIN GENERATE_SERIES(1, 6) AS v;

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

WITH joined AS (
    SELECT e.EntityId, e.Status, p.ValidFrom AS PriceFrom, p.Price,
           r.ValidFrom AS RegionFrom, r.Region
    FROM dbo.Entity AS e
    JOIN dbo.PriceHistory  AS p ON p.EntityId = e.EntityId
    JOIN dbo.RegionHistory AS r ON r.EntityId = e.EntityId
),
windowed AS (
    SELECT *,
           LAG(Price) OVER (PARTITION BY EntityId ORDER BY PriceFrom) AS PrevPrice,
           ROW_NUMBER() OVER (PARTITION BY EntityId
                              ORDER BY PriceFrom DESC, RegionFrom DESC) AS rn
    FROM joined
)
SELECT EntityId, Price, PrevPrice, Region
FROM windowed
WHERE Status = 'Active' AND rn = 1;

Он возвращает 800 строк. Но вот что происходит на каждом этапе:

1,000 сущностей
   ↓ соединение с историей цен (по 18 версий для каждой сущности)
18,000 строк
   ↓ соединение с историей регионов (по 6 версий для каждой сущности)
108,000 строк
   ↓ LAG + ROW_NUMBER → сортировка
108,000 отсортированных строк
   ↓ фильтрация: Status = 'Active' AND rn = 1
800 строк

Для каждой возвращаемой строки ядро СУБД формирует и сортирует 135 строк. Поскольку Status не входит в список секционирования оконных функций, условие отбора по этому столбцу нельзя применить до их вычисления. Поэтому ядру СУБД приходится сначала вычислить все оконные функции, а уже затем отфильтровать результаты.

Подсчитывайте строки на каждом этапе

Самый быстрый способ выяснить, на каком этапе размножаются строки, — подсчитать их количество последовательно:

SELECT COUNT(*) AS rows_, COUNT(DISTINCT e.EntityId) AS entities
FROM dbo.Entity AS e;                                                   -- 1,000 | 1,000

SELECT COUNT(*), COUNT(DISTINCT e.EntityId)
FROM dbo.Entity AS e
JOIN dbo.PriceHistory AS p ON p.EntityId = e.EntityId;                  -- 18,000 | 1,000

SELECT COUNT(*), COUNT(DISTINCT e.EntityId)
FROM dbo.Entity AS e
JOIN dbo.PriceHistory  AS p ON p.EntityId = e.EntityId
JOIN dbo.RegionHistory AS r ON r.EntityId = e.EntityId;                 -- 108,000 | 1,000

Если COUNT(*) увеличивается, а COUNT(DISTINCT key) остаётся неизменным, значит, соединение размножает строки, а не добавляет новую информацию. Это соединение «многие ко многим» между историческими таблицами.

Прежде чем оптимизировать, проверьте правильность результатов

Такое размножение строк может влиять не только на затраты ресурсов, но и на сами результаты запроса.

После второго соединения каждая версия цены встречается шесть раз. Функция LAG(Price) должна возвращать предыдущую цену, но теперь она может вернуть дубликат той же самой цены. Запрос выполняется, возвращает ожидаемое количество строк, однако некоторые значения могут оказаться неверными.

В одном из реальных случаев, который мы разбирали, дублирующиеся записи в исторических таблицах были выявлены как фактор, который мог повлиять на логику получения предыдущих значений, подобную описанной. Это была гипотеза, которую следовало проверить с учётом бизнес-правил, а не подтверждённая ошибка. В своей системе поступайте так же: сначала убедитесь, какими должны быть результаты, и только после этого пытайтесь ускорить запрос.

Более удачная структура запроса

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

WITH p AS (
    SELECT EntityId, Price,
           LAG(Price) OVER (PARTITION BY EntityId ORDER BY ValidFrom) AS PrevPrice,
           ROW_NUMBER() OVER (PARTITION BY EntityId ORDER BY ValidFrom DESC) AS rn
    FROM dbo.PriceHistory
),
r AS (
    SELECT EntityId, Region,
           ROW_NUMBER() OVER (PARTITION BY EntityId ORDER BY ValidFrom DESC) AS rn
    FROM dbo.RegionHistory
)
SELECT e.EntityId, p.Price, p.PrevPrice, r.Region
FROM dbo.Entity AS e
JOIN p ON p.EntityId = e.EntityId AND p.rn = 1
JOIN r ON r.EntityId = e.EntityId AND r.rn = 1
WHERE e.Status = 'Active';

Теперь каждая оконная функция обрабатывает только одну историческую таблицу, поэтому LAG видит каждую версию цены ровно один раз. Затем соединения объединяют по одной записи для каждой сущности, не размножая версии. Сравните фактические планы обоих запросов рядом и убедитесь, что результаты соответствуют ожидаемым бизнес-правилам.

На что ещё обратить внимание в фактическом плане выполнения

  • Предупреждения при сортировке и хешировании. Жёлтый значок предупреждения на операторе может означать, что часть данных была выгружена в tempdb, поскольку выделенного объёма памяти не хватило для фактического количества строк.
  • Распределение памяти. Проверьте свойства корневого оператора. Объём памяти, рассчитанный для 1 000 строк, не позволит эффективно обработать 108 000.
  • Запоздалая фильтрация. Если фильтр расположен ближе к левой части плана (то есть ближе к завершению обработки) и отбрасывает большую часть строк, это означает, что почти вся работа справа от него была напрасной.
  • Статистика. Используйте её как доказательство, а не как универсальное средство исправления. Если фактическое количество строк значительно отличается от расчётного при просмотре таблицы или поиске по индексу, причиной может быть устаревшая статистика. Если же на этих операторах оценки точны, а ошибка появляется после соединения, очередное обновление статистики, как правило, не поможет.

Контрольный список

  1. Получите фактический план выполнения, а не только предполагаемый.
  2. Найдите первый оператор, на котором расчётное и фактическое количество строк резко расходятся.
  3. Подсчитайте общее количество строк и количество уникальных ключей после каждого соединения.
  4. Проверьте, нет ли соединений «многие ко многим» между историческими таблицами.
  5. Убедитесь, что условия отбора применяются до дорогостоящих сортировок и оконных функций, а не после них.
  6. Проверьте, не происходит ли сброс данных во tempdb и достаточно ли выделенного объёма памяти.
  7. Прежде чем приступать к оптимизации, убедитесь в правильности результатов.
  8. Проверьте изменения на уменьшенной копии данных, а затем подтвердите улучшение производительности на полном объёме.

Итог

Самыми затратными для запроса часто оказываются строки, которые приложение в итоге никогда не получает. Прежде чем снова обновлять статистику или добавлять очередной индекс, выясните, сколько строк фактически обрабатывает каждый этап. Всё это уже видно в фактическом плане выполнения.

Комментариев нет:

Отправить комментарий