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

Подходит для:Подходит для: SQL Server 2025 (17.x) предварительная версия Azure SQL DatabaseSQL баз данных в Microsoft Fabric

Термин необязательные параметры обозначает конкретную вариацию проблемы плана, чувствительного к параметрам (PSP), при которой текущее значение параметра во время выполнения запроса определяет, нужно ли выполнить поиск в таблице или сканировать её. Простой пример может быть примерно так:

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

В этом примере SQL Server всегда выбирает план, который сканирует таблицу Table1, даже если на Table1(col1) есть индекс. План поиска может быть невозможен с значениями NULL. Методы указания запросов, такие как OPTIMIZE FOR, могут не оказаться полезными для этого типа проблемы PSP, так как в настоящее время не существует оператора, который динамически изменяет поиск индекса на сканирование во время выполнения. Такая комбинация поиска> и сканирования во время выполнения может также не быть эффективной, так как оценки кратности на вершине этого оператора, скорее всего, будут неточными. Результатом является неэффективный выбор плана и чрезмерные объемы памяти для более сложных запросов с аналогичными шаблонами запросов.

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

• где значение IS NOT NULL параметра позволяет использовать план поиска или что-то более оптимальное, чем полный план сканирования.
• где значение параметра имеет значение NULL, используется план сканирования.

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

• Предикаты равенства

  WHERE column1 = @p
  

• Динамический поиск

  WHERE column1 = @p1 OR @p1 IS NULL
    AND column2 = @p2 OR @p2 IS NOT NULL
  

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

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

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;

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

Аналогично диапазону кратности предиката, используемому оптимизацией PSP, OPPO встраивает системную подсказку, используемую в запросе, в текст запроса плана. Это указание недопустимо для использования приложением или если вы пытаетесь использовать его самостоятельно.

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

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) использует OPPO по умолчанию.

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

ALTER DATABASE [<database-name-placeholder>] SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

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

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

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

Вы можете использовать DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION подсказку запроса для отключения оптимизации плана с необязательными параметрами для данного запроса. Указания должны быть указаны с помощью 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: Расширенное событие следует тому же шаблону, что и событие с запросами с чувствительностью параметров, используемое при оптимизации PSP. Он включает дополнительные поля, доступные в улучшениях оптимизации PSP, которые состоят из отображения количества предикатов, найденных функцией интересных, более подробных сведений в формате JSON относительно интересных предикатов, а также, если OPPO поддерживается для предиката или предиката.

Замечания

• XML ShowPlan для варианта запроса будет выглядеть примерно так, как в следующем примере, где выбранные предикаты содержат соответствующие сведения, добавленные в указание optional_predicate, план PER VALUE.

<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 события

Связанный контент

• Руководство по архитектуре обработки запросов
• Перекомпилирование планов выполнения
• Повторное использование параметров и плана выполнения
• Простая параметризация
• Принудительная параметризация
• Подсказки запросов (Transact-SQL)
• Интеллектуальная обработка запросов в базах данных SQL
• Конфиденциальность параметров
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)