Краткий ответ

Это典ичный случай параметрического нюхания: при компиляции плана СУБД учитывает значение параметра, с которым запрос компилировался, а затем может переиспользовать полученный план для других значений. Если распределение данных неравномерно, план, подходящий для редкого клиента, может быть неэффективен для крупного, и наоборот.

Очистка кэша заставляет запрос компилироваться заново, поэтому первым может оказаться другое значение параметра и сформироваться другой план.

Исторический контекст

Кэширование планов появилось как способ не выполнять дорогостоящую компиляцию при каждом запуске SQL-запроса. СУБД сохраняет разобранный и оптимизированный план и повторно использует его для запросов с тем же шаблоном.

Параметризация позволяет дополнительно переиспользовать один план для разных значений. Это снижает нагрузку на CPU, но создаёт компромисс: один план не всегда одинаково хорошо подходит для всех значений параметра.

Постановка проблемы

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

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

Подробное решение

Во время компиляции оптимизатор оценивает селективность параметра по статистике и выбирает физический план: способ доступа к данным, порядок соединений, алгоритмы соединений и объём выделяемой памяти. В некоторых СУБД, например SQL Server, фактическое значение параметра при компиляции может быть использовано для таких оценок; этот механизм обычно называют parameter sniffing.

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

Минимальная иллюстрация для SQL Server:

CREATE OR ALTER PROCEDURE dbo.GetOrders
    @CustomerId int
AS
BEGIN
    SELECT OrderId, OrderDate
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;

EXEC dbo.GetOrders @CustomerId = 7;
EXEC dbo.GetOrders @CustomerId = 900000;

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

Варианты исправления зависят от характера нагрузки:

Перекомпиляция для каждого запуска позволяет подобрать план под текущее значение, но увеличивает стоимость компиляции и CPU.
Фиксация предпочтительного режима оценки делает поведение стабильнее, однако может ухудшить редкие или крупные случаи.
Разделение сильно различающихся сценариев на разные запросы или процедуры даёт специализированные планы, но усложняет код.
Механизмы нескольких планов для чувствительных параметров, если они поддерживаются конкретной версией СУБД, сохраняют несколько вариантов и выбирают подходящий.
Обновление статистики важно для качества оценок, но само по себе не гарантирует устранения проблемы: уже кэшированный план может продолжить использоваться, а распределение данных может оставаться принципиально неоднородным.

Нельзя автоматически считать очистку кэша исправлением: она лишь временно меняет момент компиляции и может нарушить работу других запросов. Выбор решения должен подтверждаться измерениями и планами выполнения.

Ситуация из практики

Отчёт по заказам обычно выполнялся быстро, но для нескольких крупных клиентов периодически занимал минуты. Анализ показал, что процедура получила план индексного поиска после запуска для небольшого клиента, а затем переиспользовала его для клиента с миллионами строк; оценки кардинальности сильно отличались от фактических.

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

Выбрали управляемое разделение сценариев после проверки распределения данных и планов. Для редких клиентов сохранили индексный путь, а для крупных использовали отдельный запрос с подходящим планом. После внедрения проверили обе группы параметров в нагрузочном тесте; нестабильность времени ответа исчезла без глобальной очистки кэша.

Что кандидаты часто упускают

1. Достаточно ли обновить статистику, чтобы устранить параметрическое нюхание?

Нет. Обновление статистики может улучшить оценки при следующей компиляции, но не обязательно немедленно заменяет уже кэшированный план. Кроме того, даже свежая статистика не делает один план оптимальным для параметров с принципиально разной селективностью.

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

2. Почему замена параметра на литерал иногда меняет производительность?

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

Такой подход нельзя применять без контроля: динамический SQL с внешними данными должен быть безопасно параметризован, иначе появляется риск SQL-инъекции. Даже без риска безопасности чрезмерное разнообразие текстов запросов может увеличить давление на кэш и CPU.

3. Как отличить параметрическое нюхание от просто отсутствующего индекса?

Нужно сравнить выполнения одного запроса для разных параметров и посмотреть фактические планы. Признаки чувствительности к параметру — заметно разные фактические объёмы строк, один и тот же переиспользованный план и сильное изменение стоимости при смене значения; при этом для разных значений могут быть разумны разные стратегии доступа.

Отсутствующий индекс обычно проявляется как устойчиво дорогой доступ для большинства значений, а не как резкая зависимость от первого запуска. Диагностику дополняют данными Query Store, логическими чтениями, временем компиляции и экспериментом с контролируемой перекомпиляцией, а не только просмотром списка индексов.