Новое в SQL Server 2025: оптимизация плана с опциональными параметрами (OPPO)

Автор: Luca Biondi, SQL SERVER 2025 Optional Parameter Plan Optimization (OPPO). What It Is and Benchmarks — Internal Part 1 Series

SQL Server 2025 наконец атакует одного из старейших убийц производительности: запросы с необязательными параметрами и нестабильные планы выполнения. В этой статье вы узнаете, как OPPO работает изнутри, почему это важно для нагрузок, чувствительных к параметрам, и как провести собственные бенчмарки с реальными тестами T-SQL.

В двух словах

✅ SQL Server 2025 OPPO автоматически создаёт несколько вариантов планов выполнения для запросов с необязательными параметрами

✅ OPPO является частью Intelligent Query Processing и снижает проблемы планов, чувствительных к параметрам 

✅ Запросы, использующие шаблоны вида (@Param IS NULL OR Column = @Param), теперь могут получать оптимизированные планы 

✅ Бенчмарки показывают резкое улучшение использования CPU, логических чтений и стабильности выполнения 

Более 20 лет опциональные или другими словами, необязательные параметры были одним из злейших тихих убийц производительности в приложениях SQL Server.

Классический шаблон:

WHERE Column = @Parameter
OR @Parameter IS NULL

выглядит невинно.

⚠️ Но изнутри он создаёт кошмар для оптимизатора.

SQL Server должен сгенерировать ОДИН план, пытаясь удовлетворить ОБА сценария:

• Высокоселективные запросы с seek
• Запросы с большим сканированием

Обычно это означает:

• Плохие оценки кардинальности
• Неверные стратегии соединения
• Избыточные выделения памяти
• Всплески CPU
• Нестабильность кэша планов

Что такое OPPO на самом деле

Optimization of Plans for Optional Parameters (OPPO) — это новая функция Intelligent Query Processing в SQL Server 2025, предназначенная для автоматического решения проблемы необязательных предикатов, чувствительных к параметрам. Вместо генерации одного компромиссного плана выполнения SQL Server теперь может создавать несколько специализированных планов. Это поведение концептуально похоже на оптимизацию Parameter Sensitive Plan (PSP), представленную ранее, но сосредоточено именно на необязательных предикатах.

Официальная документация Microsoft:

• Microsoft Learn — Optimization of Plans for Optional Parameters

Старая проблема до OPPO

Рассмотрим эту классическую хранимую процедуру:

CREATE OR ALTER PROCEDURE dbo.SearchOrders
(
    @CustomerID INT = NULL
)
AS
BEGIN

    SELECT
        SalesOrderID,
        CustomerID,
        OrderDate,
        TotalDue
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID
      OR @CustomerID IS NULL;

END
GO

Этот запрос внутри ОДНОГО плана выполнения смешивает:

• Селективные поиски
• Полные сканирования таблиц

Результат:

• Плохое переиспользование плана
• Нестабильность из-за sniffing параметров
• Огромный разброс производительности

ДИАГНОСТИКА — почему SQL Server страдал

До OPPO SQL Server должен был оценивать:

• Сколько строк совпадёт
• Использовать seek или scan
• Сколько памяти выделить

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

• Давления на CPU
• Раздувания выделения памяти
• Случайных медленных запросов
• Нестабильности планов выполнения

ЗАПРОС — бенчмарк без OPPO

Запустите этот бенчмарк вручную.

-- Настройка бенчмарка

ALTER DATABASE CURRENT
SET COMPATIBILITY_LEVEL = 160;
GO

ALTER DATABASE SCOPED CONFIGURATION 
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;
GO

DBCC FREEPROCCACHE;
GO

SET STATISTICS IO, TIME ON;
GO

EXEC dbo.SearchOrders @CustomerID = 11000;
GO

EXEC dbo.SearchOrders @CustomerID = NULL;
GO

Наблюдайте:

• Логические чтения
• Время CPU
• Поведение переиспользования плана

Обычно один сценарий становится драматически медленнее.

Включение OPPO

Теперь включим SQL Server 2025 OPPO.

-- Включить OPPO

ALTER DATABASE CURRENT
SET COMPATIBILITY_LEVEL = 170;
GO

ALTER DATABASE SCOPED CONFIGURATION 
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;
GO

DBCC FREEPROCCACHE;
GO

Уровень совместимости 170 обязателен.

ЗАПРОС — бенчмарк с OPPO

SET STATISTICS IO, TIME ON;
GO

EXEC dbo.SearchOrders @CustomerID = 11000;
GO

EXEC dbo.SearchOrders @CustomerID = NULL;
GO

Теперь вы должны наблюдать:

• Разные варианты планов
• Более точные оценки кардинальности
• Меньше логических чтений
• Меньше использования CPU
• Более стабильное время выполнения

Внутреннее поведение

Изнутри SQL Server теперь распознаёт необязательные предикаты как шаблоны, чувствительные к параметрам. Вместо навязывания одного универсального плана он может создавать:

• План, ориентированный на seek
• План, ориентированный на scan

SQL Server динамически направляет выполнение к лучшему варианту плана в зависимости от значений параметров во время выполнения. Это драматически улучшает качество планов.

Сравнение результатов

CL 170 (OPPO включён)

Селективный запрос (CustomerID = 11000)

Logical Reads: 4
CPU: 0 ms
Elapsed Time: 15–20 ms

Запрос с полным сканированием (CustomerID = NULL)

Rows Returned: 20164
Logical Reads: 87
CPU: 0 ms
Elapsed Time: 78–79 ms

План выполнения с OPPO. Один и тот же запрос с разными параметрами имеет разные планы! ВОТ ЭТО ДА!

CL 160 (OPPO отключён)

Селективный запрос (CustomerID = 11000)

Logical Reads: 39
CPU: 0 ms
Elapsed Time: 13–14 ms

Запрос с полным сканированием (CustomerID = NULL)

Rows Returned: 20164
Logical Reads: 40365
CPU: 31 ms
Elapsed Time: 88–89 ms

План выполнения без OPPO. У нас один и тот же план при разных параметрах запроса.

---

Ключевые различия

Метрика	CL 170 (OPPO)	CL 160	Улучшение
Логические чтения	87	40365	~460x сокращение
Время CPU	0 ms	31 ms	Устранено
Затраченное время	~79 ms	~89 ms	Сопоставимо (небольшой набор данных)

---

Глубокий анализ

CL 170 (OPPO включён)

• SQL Server создаёт несколько планов выполнения
• Index Seek используется для селективных запросов
• Эффективное сканирование используется для запросов по всему набору данных
• Точная оценка кардинальности
• Минимальные логические чтения

CL 160 (OPPO отключён)

• Один план выполнения переиспользуется для всех сценариев
• Компромиссное качество плана
• Огромное количество ненужных логических чтений
• Более высокое использование CPU
• Плохая масштабируемость под нагрузкой

Важные оговорки

OPPO — НЕ магия.

Всё ещё существуют сценарии, где динамический SQL или ручное ветвление запросов его превосходят.

Примеры:

• Очень сложные предикаты
• Многоколоночные необязательные фильтры
• Сильно скошенные распределения
• Экстремальная волатильность данных

Но для многих легаси-хранимых процедур OPPO — это огромный выигрыш с нулевыми изменениями кода.

Моя рабочая стратегия

После 25 лет настройки систем SQL Server вот моё практическое правило:

OPPO — одно из крупнейших реальных улучшений оптимизатора со времён оптимизации Parameter Sensitive Plan. Но никогда не доверяйте автоматизации слепо.

Мой рекомендованный процесс:

• Сначала включите Query Store
• Проведите бенчмарк до/после
• Внимательно следите за выделением памяти
• Проверьте стабильность планов под конкуренцией
• Проверьте рост кэша планов

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

ВЫВОДЫ

• OPPO решает одну из старейших проблем оптимизации необязательных параметров в SQL Server
• SQL Server 2025 теперь может автоматически генерировать несколько планов выполнения
• Учитываются необязательные предикаты, ранее вызывавшие катастрофические проблемы из-за sniffing параметров
• OPPO может кардинально снизить CPU, I/O и нестабильность выполнения
• Бенчмаркинг обязателен перед развёртыванием в продакшене