Parameter sniffing в SQL Server: сначала диагностика

Хранимая процедура выполняется за 80 миллисекунд для одного клиента и за 40 секунд для другого. Код не менялся, сервер не перегружен, а повторный запуск иногда будто бы устраняет проблему. Это типичный признак плана, чувствительного к параметрам, или parameter sniffing.

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

Почему один план может не подойти

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

Неравномерные данные, необязательные фильтры, устаревшая статистика и коррелированные столбцы повышают риск.

Безопасное воспроизведение

Начните с типичной процедуры:

CREATE OR ALTER PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC;
END;

В непроизводственной среде проверьте селективное и неселективное значение. Сохраните фактический план выполнения и статистику для обоих запусков. Если скорость зависит от значения, при котором был скомпилирован кешированный план, это серьезное доказательство чувствительности к параметрам.

Не очищайте весь кеш планов в production. Это вызовет всплеск компиляций и затронет несвязанные нагрузки.

Практический процесс диагностики

1. Найдите в Query Store планы с большим разбросом длительности, CPU или логических чтений.
2. Сравните фактическое и оценочное число строк на первом важном join или lookup.
3. Запишите значения параметров при компиляции и выполнении.
4. Проверьте гистограмму и время обновления соответствующей статистики.
5. Исключите неявные преобразования, несаргируемые предикаты и отсутствующие индексы.
6. Убедитесь, что быстрый и медленный запуски имеют одинаковую идентичность запроса и плана.

Главный вопрос не "Плохой ли это план?", а "Хорош ли он для одной важной формы данных и плох для другой?"

Выбор минимального надежного решения

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

В SQL Server 2022 и новее оптимизация Parameter Sensitive Plan может хранить несколько вариантов плана для подходящих предикатов равенства. Проверьте уровень совместимости и форму запроса, затем убедитесь в наличии вариантов через Query Store.

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

Используйте OPTION (RECOMPILE), если компиляция дешева и каждому запуску нужен план под конкретные значения. Применяйте ее к минимально возможному оператору. OPTIMIZE FOR подходит только при стабильном репрезентативном значении или при сознательном выборе усредненного плана. Динамический SQL с sp_executesql полезен для необязательных фильтров: разные формы запроса получают разные планы без отказа от параметризации.

Типичные ошибки

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

Чек-лист для production

Снимите исходные показатели, сохраните первоначальный план, протестируйте репрезентативные группы параметров, измерьте CPU и логические чтения, разверните самое узкое изменение и наблюдайте Query Store. Заранее определите откат.

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