Sniffing параметров в SQL Server

Автор: Vivek Johari, Parameter Sniffing in SQL Server

"Parameter Sniffing" (конфиденциальность параметров) в SQL Server происходит, когда план выполнения запроса генерируется с использованием конкретных значений параметров. Последующие выполнения того же запроса могут работать плохо с другими значениями параметров из-за неподходящего кэшированного плана. Вот как можно решить эту проблему:

1. Использование подсказок запроса (Query Hints)

SQL Server предоставляет подсказки запроса (hints) для управления поведением выполнения запроса.

OPTIMIZE FOR UNKNOWN

Эта подсказка указывает SQL Server игнорировать конкретные значения параметров. Вместо этого используется общий план при генерации плана выполнения.

SELECT *
FROM Employees
WHERE Department = @Department
OPTION (OPTIMIZE FOR UNKNOWN);

OPTIMIZE FOR (оптимизация для конкретного значения)

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

SELECT *
FROM Employees
WHERE Department = @Department
OPTION (OPTIMIZE FOR (@Department = 'IT'));

2. Использование параметра RECOMPILE

Опция OPTION (RECOMPILE) принуждает SQL Server генерировать новый план для каждого выполнения запроса. Этот подход позволяет избежать зависимости от кэшированного плана.

SELECT *
FROM Employees
WHERE Department = @Department
OPTION (RECOMPILE);

Кроме того, можно применить WITH RECOMPILE на уровне хранимой процедуры:

CREATE PROCEDURE GetEmployees
    @Department NVARCHAR(50)
WITH RECOMPILE
AS
BEGIN
    SELECT *
    FROM Employees
    WHERE Department = @Department;
END;

Преимущества: Гарантирует, что всегда используется наилучший план выполнения.
Недостатки: Дополнительная нагрузка на ЦП из-за повторной компиляции.

3. Использование локальных переменных

Использование локальных переменных предотвращает sniffing параметров SQL Server, так как план запроса будет предполагать неизвестные значения.

CREATE PROCEDURE GetEmployees
    @Department NVARCHAR(50)
AS
BEGIN
    DECLARE @LocalDepartment NVARCHAR(50) = @Department;

    SELECT *
    FROM Employees
    WHERE Department = @LocalDepartment;
END;

Преимущества: Принуждает SQL Server создавать более общий план.
Недостатки: Может приводить к неоптимальным планам в некоторых случаях.

4. Использование руководства плана (Plan Guides)

Руководства планов позволяют влиять на план выполнения для запроса без изменения самого запроса.

1. Идентифицируйте запрос с помощью sp_create_plan_guide.
2. Примените подсказки, такие как OPTIMIZE FOR или OPTION (RECOMPILE), используя руководство планом.

5. Использование хранилища запросов (Query Store)

Функция хранилища запросов (доступна в SQL Server 2016 и более поздних версиях) позволяет отслеживать производительность запросов и принудительно применять конкретные планы выполнения.

• Шаги:
  1. Определите запрос, вызывающий проблемы с sniffing параметров.
  2. Используйте хранилище запросов для принудительного применения хорошего плана.
  3. Отслеживайте производительность, чтобы убедиться, что выбранный план работает для большинства значений параметров.

6. Секционированные представления или динамический SQL

• Секционированные представления: Разбейте запрос на несколько запросов с фиксированными значениями параметров, которые SQL Server будет оптимизировать отдельно.
• Динамический SQL: Динамически конструируйте инструкцию SQL, чтобы гарантировать создание нового плана для каждого выполнения.

CREATE PROCEDURE GetEmployees
    @Department NVARCHAR(50)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX);

    SET @SQL = N'SELECT * FROM Employees WHERE Department = ''' + @Department + '''';
    EXEC sp_executesql @SQL;
END;

7. Статистика и настройка индексов

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

8. Параметризованные запросы в приложениях

Для запросов на уровне приложения обеспечьте согласованное использование параметров, чтобы снизить вариативность в планах выполнения.

Заключение

Лучшее решение проблемы sniffing-а параметров зависит от вашей конкретной рабочей нагрузки и шаблонов запросов. OPTIMIZE FOR UNKNOWN или RECOMPILE — хорошие отправные точки для быстрых исправлений. Рассмотрите более продвинутые подходы, такие как хранилище запросов или руководства планов, для постоянных проблем. Регулярный мониторинг и обновление вашей базы данных (например, статистики, индексов) также помогает минимизировать эффекты sniffing параметров.