Оптимизация чувствительных к параметрам планов исполнения в SQL Server 2022

Автор: Deepam Ghosh, Parameter Sensitive Plan Optimization in SQL Server 2022

SQL Server 2022 включает множество усовершенствований и новых возможностей по сравнению с предыдущими версиями. Среди них новые роли сервера, улучшенный Query Store, повышение производительности TempDB, интеллектуальная обработка запросов, автономные группы доступности, Database Ledger и многое другое.

В сегодняшней статье мы рассмотрим практическую демонстрацию одной из таких возможностей — оптимизации планов, чувствительных к параметрам (Parameter Sensitive Plan Optimization, PSPO). Мы увидим, какие трудности создают параметризованные хранимые процедуры в старых версиях и как оптимизация PSPO решает эти проблемы и улучшает планы выполнения запросов в новой версии.

Эта статья посвящена только практической части, для изучения подробной теории об оптимизации планов, чувствительных к параметрам, обратитесь к официальной документации Microsoft.

Проблемы параметрического зондирования в старых версиях

Возможность PSPO предназначена для решения проблемы параметрического зондирования в параметризованных запросах. SQL Server кэширует планы выполнения этих запросов на основе значения параметра при первом запуске. Это и создает проблему «параметрического зондирования»: сгенерированные планы основаны на начальном параметре, который может быть не оптимален при последующих выполнениях запроса с другими параметрами, особенно при неравномерном распределении данных.

Демонстрация с искусственно смещёнными данными

Рассмотрим пример, в котором поэкспериментируем с параметрами и понаблюдаем за поведением SQL Server.

Примечание: В этой статье для демонстрации используется SQL Server 2022 Enterprise Edition.

Скрипт ниже создаёт таблицу Users, содержащую сведения о регистрации постояльцев в отелях по четырём разным городам Индии. В неё вставляется 1 миллион записей, которые специально добавляются с неравномерным распределение по количеству.

CREATE TABLE dbo.Users(
    ID INT,
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    City NVARCHAR(100),
    HotelName NVARCHAR(100),
    CheckInDate DATE
);
GO

;WITH Numbers AS (
    SELECT TOP (1000000) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowID
    FROM sys.all_objects a
    CROSS JOIN sys.all_objects b
)
INSERT INTO dbo.Users(ID, FirstName, LastName, City, HotelName, CheckInDate)
SELECT 
    RowID,
    'Rahul',
    CASE WHEN RowID % 2 = 1 THEN 'Sharma' ELSE 'Pandey' END,
    CASE 
        WHEN RowID % 10 = 1 THEN 'Udaipur'
        WHEN RowID % 1000 = 5 THEN 'Jaipur'
        WHEN RowID % 1000000 = 3 THEN 'New Delhi'
        ELSE 'Agra'
    END,
    CASE WHEN RowID % 2 = 1 THEN 'Oberoi' ELSE 'Taj' END,
    DATEADD(DAY, -1 * (RowID % 100), CAST(GETDATE() AS DATE))
FROM Numbers;
GO

Теперь создадим некластерный индекс по столбцу City:

CREATE INDEX IX_City
ON dbo.Users(City)
GO

После добавления индекса выполним запрос для анализа распределения данных по городам:

SELECT City AS TravelDestination, COUNT(*) AS NoOfVisitors
FROM dbo.Users 
GROUP BY City
ORDER BY NoOfVisitors
GO

Мы видим существенную разницу: для «New Delhi» только 1 запись, а для «Agra» — 898999 записей.

Создание параметризованной хранимой процедуры

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

CREATE OR ALTER PROCEDURE dbo.UserDestination
  @City NVARCHAR(100)
AS
BEGIN
  SELECT TOP 1000 * FROM dbo.Users WHERE City = @City
  ORDER BY ID
END
GO

Очистим кэш планов, включим сбор статистики и установим уровень совместимости базы данных равным 130 (SQL 2016), чтобы увидеть поведение в старой версии:

DBCC FREEPROCCACHE
GO

SET STATISTICS IO, TIME ON
GO

ALTER DATABASE DBTest SET COMPATIBILITY_LEVEL = 130  -- SQL 2016
GO

Выполним хранимую процедуру с разными параметрами:

USE DBTest

EXEC dbo.UserDestination 'New Delhi';  -- 1 Row(s)
EXEC dbo.UserDestination 'Jaipur';     -- 1000 Row(s)
EXEC dbo.UserDestination 'Udaipur';    -- 100000 Row(s)
EXEC dbo.UserDestination 'Agra';       -- 898999 Row(s)
GO

Изображение 1. На плане выполнения видно, что для всех параметров используется один и тот же план: Index Seek с поиском по Heap, затем соединение и сортировка.

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

DBCC FREEPROCCACHE
GO

USE DBTest

EXEC dbo.UserDestination 'Agra';       -- 898999 Row(s)
EXEC dbo.UserDestination 'New Delhi';  -- 1 Row(s)
EXEC dbo.UserDestination 'Jaipur';     -- 1000 Row(s)
EXEC dbo.UserDestination 'Udaipur';    -- 100000 Row(s)
GO

Изображение 2. В этот раз все параметры используют другой общий план: полное сканирование таблицы, сортировка, параллелизм и выборка первых 1000 строк.

Как Parameter Sniffing влияет на планы

В первом случае, когда процедура запускалась с параметром «New Delhi», SQL использовал его для генерации плана. Так как там всего 1 запись, план оказался оптимальным (Index Seek и поиск строк). Но этот же план применился и для других параметров, включая «Agra» с почти 900 тыс. строк, что крайне неэффективно.

Во втором случае, при первом запуске с «Agra», SQL создал оптимальный план для большого количества строк — сканирование таблицы. Но затем тот же план применился и к параметру «New Delhi», где достаточно было бы быстрого поиска, и опять получилась неэффективность.

Оптимизация PSPO в SQL Server 2022

Теперь посмотрим, как PSPO решает эту проблему в SQL Server 2022.

DBCC FREEPROCCACHE
GO

ALTER DATABASE DBTest SET COMPATIBILITY_LEVEL = 160  -- SQL 2022
GO

USE DBTest

EXEC dbo.UserDestination 'New Delhi';  -- 1 Row(s)
EXEC dbo.UserDestination 'Jaipur';     -- 1000 Row(s)
EXEC dbo.UserDestination 'Udaipur';    -- 100000 Row(s)
EXEC dbo.UserDestination 'Agra';       -- 898999 Row(s)
GO

Теперь SQL Server не использует первый параметр для создания единственного плана. Вместо этого он создаёт несколько планов, ориентированных на разные диапазоны количества строк. Для «New Delhi» (1 строка) оптимальным стал Index Seek с поиском по Heap, соединением и сортировкой. Для «Agra» (898999 строк) оптимальным оказался план сканирования таблицы, сортировки, параллелизма и выборки 1000 строк. В обоих случаях использован наилучший план.

SQL Server теперь кэширует три варианта планов (низкий, средний и высокий) в зависимости от диапазонов кардинальности. При этом в запрос добавляется внутренний хинт OPTION(PLAN PER VALUE(...)), содержащий идентификатор варианта QueryVariantID и диапазон предиката (predicate_range), указывающий диапазон количества строк, для которого эффективен данный план.

Включение и отключение PSPO

По умолчанию PSPO включена в SQL Server 2022. При необходимости её можно отключить для конкретной базы данных:

ALTER DATABASE [DBTest]
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

Чтобы включить её снова:

ALTER DATABASE [DBTest]
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;

Ограничения PSPO

• Кэширование и поддержка нескольких планов выполнения для одного параметризованного запроса увеличивает нагрузку на память, процессор и размер кэша планов. Это может быть критично для систем с ограниченными ресурсами.
• Если данные не имеют выраженной неоднородности по диапазонам, PSPO не работает. Она уменьшает проблему Parameter Sniffing, но не является универсальным решением. При трёх вариантах планов возможны новые случаи неоптимального выбора. Однако в большинстве ситуаций PSPO значительно повышает производительность.

Заключение

В итоге, PSPO — ценная функция SQL Server 2022, которая существенно улучшает производительность запросов. Она в значительной мере уменьшает проблему Parameter Sniffing. Но важно понимать, что данная возможность не всегда подходит для всех запросов. Внимательный анализ и отключение функции там, где она не нужна, помогут обеспечить оптимальную производительность SQL Server.