SQL Server Parameter Sniffing: ошибка, которая не является ошибкой (но всё ломает)

Автор: Luca Biondi, SQL Server Parameter Sniffing: The Bug That Isn’t a Bug (But Breaks Everything)

В этой статье вы узнаете, как выявить и победить нестабильную производительность в SQL Server. Если ваши запросы без предупреждения переходят от «молниеносных» к «еле ползущим», вы стали жертвой Parameter Sniffing. Пришло время вернуть контроль над вашими планами выполнения и остановить непредсказуемость.

Ваш запрос работает быстро, а затем внезапно… становится медленным!
У вас есть:

• Тот же запрос.
• Те же данные.
• Тот же сервер.

👉 Что изменилось?

💣 Parameter Sniffing. И нет… это НЕ ошибка. Это то, как SQL Server был спроектирован для работы.

🧠 Что такое Parameter Sniffing на самом деле

Когда SQL Server компилирует запрос или хранимую процедуру, он использует первое значение параметра для создания плана выполнения. Это значение «подсматривается» (sniffed) и используется для оптимизации.

Проблема?

👉 Этот план переиспользуется… даже когда параметры меняются.

💣 У нас один план для множества реальных ситуаций.

🔥 Когда это происходит

Это происходит для:

• Хранимых процедур
• Параметризованных запросов
• Сильно перекошенных распределений данных
• Различных значений селективности

👉 Пример:

SELECT *
FROM Orders
WHERE CustomerID = @CustomerID;

Если у нас есть, скажем, перекошенные данные, где:

• У клиента A всего 1 строка
• У клиента B — 1 000 000 строк

💣 Один и тот же запрос должен иметь совершенно разные оптимальные планы.

⚔️ Настоящая проблема

SQL Server выбирает ОДИН план на основе первого выполнения. И слепо переиспользует его. Вот почему запросы становятся нестабильными.

• Быстро иногда
• Медленно в другое время
• Невозможно предсказать

🧪 Бенчмарк (реальный сценарий)

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- Первое выполнение (маленький набор данных)
EXEC GetOrders @CustomerID = 1;

-- Второе выполнение (большой набор данных)
EXEC GetOrders @CustomerID = 9999;

Сценарий	Время ЦП	Чтений	План
Маленький набор → План в кэше	50 мс	200	Поиск по индексу
Большой набор → Повторно использованный план	2000 мс	500000	Поиск по индексу ❌

💣 Тот же план - неправильный выбор.

🔥 Реальные исправления

1️⃣ OPTION (RECOMPILE)

SELECT *
FROM Orders
WHERE CustomerID = @CustomerID
OPTION (RECOMPILE);

• ✔️ Всегда оптимальный план
• ❌ Накладные расходы на ЦП (перекомпиляция)

2️⃣ OPTIMIZE FOR

OPTION (OPTIMIZE FOR (@CustomerID = 1));

• ✔️ Стабильный план
• ❌ Не адаптивный

3️⃣ OPTIMIZE FOR UNKNOWN

OPTION (OPTIMIZE FOR UNKNOWN);

• ✔️ Использует среднее распределение
• ✔️ Безопаснее для смешанных нагрузок

🚀 РАБОТАЮЩАЯ стратегия (самая важная часть)

Перестаньте думать в терминах «исправления», думайте вместо этого о характере рабочей нагрузки.

• Мало выполнений → RECOMPILE
• Предсказуемая нагрузка → OPTIMIZE FOR
• Смешанная нагрузка → UNKNOWN
• Критически важные запросы → собственная логика / динамический SQL

Имейте в виду, что НЕ существует универсального решения. Есть только компромиссы.

Последняя мысль

👉«Parameter Sniffing — это не ошибка… это стратегия оптимизации». Но в неправильном сценарии она становится вашим худшим кошмаром.