Оптимизация плана с опциональными параметрами (OPPO)

Относится к: SQL Server 2025 (17.x) Azure SQL Database SQL Database в Microsoft Fabric

Оптимизация плана необязательных параметров (OPPO) улучшает качество плана запросов для запросов, включающих необязательные параметры. Оптимальный план выполнения этих запросов зависит от того, равно ли значение параметра NULL во время выполнения. Термин необязательный параметр относится к определенной вариации проблемы плана, зависящего от параметров (PSP), в которой значение параметра во время выполнения определяет, должен ли запрос использовать поиск или сканирование.

Обзор

Запросы, использующие необязательные параметры, часто включают предикаты, которые условно применяют фильтры на основе того, предоставляется ли значение параметра. Распространенный шаблон выглядит следующим образом:

SELECT column1,
       column2
FROM Table1
WHERE (column1 = @p
       OR @p IS NULL);

При @p IS NOT NULLпоиске col1 индекса часто является наиболее эффективным планом выполнения. Когда @p IS NULL, предикат оценивается как TRUE, и может быть более целесообразным рассмотреть возможность сканирования. Без OPPO ядро СУБД SQL Server должно компилировать и кэшировать один план выполнения, допустимый для обоих случаев. Поскольку план на основе поиска недействителен, когда @p IS NULLоптимизатор часто выбирает консервативный план на основе сканирования для всех выполнений. Этот выбор может привести к неэффективному выбору плана и чрезмерному использованию ресурсов для выборочных выполнений.

Традиционные методы указания, такие как не эффективны в этом сценарии, так как OPTIMIZE FOR план должен оставаться правильным для обоих состояний параметров.

OPPO использует инфраструктуру оптимизации адаптивного плана (Multiplan), введенную с помощью оптимизации плана с учетом параметров (PSP). Эта инфраструктура создает и кэширует несколько планов выполнения для одной инструкции, что позволяет OPPO делать различные предположения на основе значений параметров, используемых в запросе.

Терминология и принцип работы

OPPO строится на платформе оптимизации адаптивного плана (Multiplan), которая также используется в оптимизации параметрически чувствительных планов. С помощью Multiplan ядро СУБД может создавать и кэшировать несколько планов выполнения для одного запроса.

Когда ядро СУБД обнаруживает подходящий необязательный шаблон параметров, он создает:

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

Во время выполнения:

Ядро СУБД оценивает значение параметра.
Диспетчер Multiplan выбирает соответствующий вариант запроса.
Выбранный вариант запроса выполняется.

После выбора варианта запроса ядро СУБД упрощает предикаты на основе фактического значения параметра. Рассмотрим следующее выражение:

@p1 IS NULL

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

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

Оптимизация OPPO и PSP решает различные варианты параметрических плановых проблем.

Оптимизация PSP выбирает планы на основе предполагаемых различий кардинальности для равенства или предикатов для диапазонов.
OPPO выбирает планы в зависимости от того, является NULLли значение параметра.

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

Поддерживаемые шаблоны запросов

Дополнительная оптимизация плана параметров применяется к запросам, в которых NULL проверки параметров влияют на допустимость плана выполнения. Например, рассмотрим веб-форму для риэлторской компании, которая позволяет использовать необязательную фильтрацию по числу спален для конкретного объявления. OPPO применяется к дизъюнктивным предикатам необязательных параметров, например:

SELECT *
FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

Даже если маркеры параметров могут обнаружить @bedrooms = 10 параметр, и вы знаете, что кардинальность для числа спален, скорее всего, будет очень низкой, оптимизатор не создает план, который использует индекс, находящийся в столбце спальни, потому что это не допустимый план для случая, где @bedrooms находится NULL. Созданный план не включает сканирование индекса.

Представьте, можно ли переписать этот запрос как две отдельные инструкции. В зависимости от значения среды выполнения параметра можно оценить следующий пример:

IF @bedrooms IS NULL
    SELECT *
    FROM Properties;
ELSE
    SELECT *
    FROM Properties
    WHERE bedrooms = @bedrooms;

Эта функция может достичь этого с помощью инфраструктуры Multiplan, которая позволяет создать план диспетчера, в котором отправляется вариант запроса.

OPPO внедряет системно-сгенерированную PLAN PER VALUE подсказку запроса (optional_predicate) в метаданные плана для связывания каждого варианта запроса с его состоянием параметров. Это указание создается системой и внедряется в текст запроса в плане. Это указание недопустимо для использования приложением или применения вручную.

Продолжая работу с предыдущим примером,

SELECT *
FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

OPPO может создать два варианта запроса, которые могут содержать следующие атрибуты, добавленные в файл Showplan XML:

@bedrooms равно NULL. Вариант запроса свертывает предикаты на основе значения параметра, что позволяет создавать план на основе сканирования.

SELECT * FROM Properties PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 1, optional_predicate(@bedrooms имеет значение NULL))
@bedrooms IS NOT NULL

SELECT * FROM Properties WHERE bedrooms = @bedrooms PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 2, optional_predicate(@bedrooms равно NULL))

Использование необязательной оптимизации плана параметров

Чтобы включить OPPO для базы данных, необходимы следующие предварительные требования:

База данных должна использовать уровень совместимости 170.
Конфигурация с привязкой к базе данных должна быть включена.

OPTIONAL_PARAMETER_OPTIMIZATION Конфигурация с областью базы данных включена по умолчанию, поэтому база данных с уровнем совместимости 170 (по умолчанию в SQL Server 2025 (17.x)) использует OPPO по умолчанию.

Вы можете убедиться, что база данных использует OPPO в SQL Server 2025 (17.x), выполнив следующие инструкции:

ALTER DATABASE [<database-name-placeholder>]
SET COMPATIBILITY_LEVEL = 170;

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Чтобы отключить OPPO для базы данных, отключите конфигурацию, связанную с OPTIONAL_PARAMETER_OPTIMIZATION базой данных.

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Использование необязательной оптимизации плана параметров с помощью указания запросов

DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION Используйте параметр запроса, чтобы отключить OPPO для данного запроса. Укажите подсказку при помощи предложения USE HINT. Дополнительные сведения см. в подсказках к запросам.

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

Укажите подсказку DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION запроса непосредственно в запросе или с помощью подсказок хранилища запросов.

Расширенные события

Используйте следующие расширенные события для устранения неполадок и диагностики. Эти события не требуются для использования функции.

optional_parameter_optimization_skipped_reason: происходит, когда OPPO решает, что запрос не подходит для оптимизации. Это расширенное событие следует тому же шаблону, что и событие parameter_sensitive_plan_optimization_skipped_reason, используемое для оптимизации PSP. Так как запрос может генерировать как варианты оптимизации PSP, так и варианты запросов OPPO, проверьте оба события, чтобы понять, почему одна или ни одна функция не задействована.

В следующем запросе показаны все возможные причины пропуска PSP:
```
SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = 'opo_skipped_reason_enum'
ORDER BY map_key;
```
query_with_optional_parameter_predicate: Это расширенное событие следует тому же шаблону, как событие query_with_parameter_sensitivity, которое использует оптимизация PSP. Он включает дополнительные поля, которые доступны в рамках улучшений для оптимизации PSP.

Эти поля отображаются:
- количество предикатов, которые свойство обнаружило интересными,
- дополнительные сведения в формате JSON относительно интересных предикатов и
- поддерживается ли OPPO для предиката или предикатов.

Remarks

ShowPlan XML для варианта запроса выглядит следующим образом. Предикаты, которые выбирает функция, содержат соответствующие сведения, добавленные в подсказку PLAN PER VALUE (optional_predicate).

<Batch>
  <Statements>
    <StmtSimple StatementCompId="4" StatementEstRows="1989" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="170" StatementSubTreeCost="0.0563916" StatementText="SELECT PropertyId, AgentId, ListingPrice, ZipCode, SquareFootage, &#xD;&#xA;           Bedrooms, Bathrooms, ListingDescription&#xD;&#xA;    FROM dbo.Property &#xD;&#xA;    WHERE (@AgentId IS NULL OR AgentId = @AgentId)&#xD;&#xA;      AND (@ZipCode IS NULL OR ZipCode = @ZipCode)&#xD;&#xA;      AND (@MinPrice IS NULL OR ListingPrice &gt;= @MinPrice)&#xD;&#xA;      AND (@HasDescription IS NULL OR &#xD;&#xA;           (@HasDescription = 1 AND ListingDescription IS NOT NULL) OR&#xD;&#xA;           (@HasDescription = 0 AND ListingDescription IS NULL)) option (PLAN PER VALUE(ObjectID = 1269579561, QueryVariantID = 7, optional_predicate(@MinPrice IS NULL),optional_predicate(@ZipCode IS NULL),optional_predicate(@AgentId IS NULL)))" StatementType="SELECT" QueryHash="0x2F701925D1202A9F" QueryPlanHash="0xBA0B2B1A18AF1033" RetrievedFromCache="true" StatementSqlHandle="0x09000033F4BE101B2EE46B1615A038D422710000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="1269579561" StatementParameterizationType="1" SecurityPolicyApplied="false">
      <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
      <Dispatcher>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@MinPrice] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@MinPrice" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@ZipCode] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@ZipCode" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@AgentId] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
      </Dispatcher>
      <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="1" CompileCPU="1" CompileMemory="376" QueryVariantID="7">

Пример выходных данных расширенного query_with_optional_parameter_predicate события:

Field	Value
`optional_parameter_optimization_supported`	True
`optional_parameter_predicate_count`	3
`predicate_details`	`{"Predicates":[{"Skewness":1005.53},{"Skewness":1989.00},{"Skewness":1989.00}]}`
`query_type`	193

Соответствие запросам и ограничения

OPPO применяется только к запросам, которые подходят для Multiplan-оптимизации. Эта функция не применяется в сценариях, которые включают:

Запросы, использующие локальные переменные вместо параметров
Запросы, скомпилированные с помощью OPTION (RECOMPILE)
Запросы, выполняемые с помощью SET ANSI_NULLS OFF
Автоматически параметризованные операторы

Кері байланыс

Бет пайдалы болды ма?

Last updated on 2026-01-07