Optimización de parámetros del plan opcional (OPPO)

Se aplica a:SQL Server 2025 (17.x) Versión preliminarBase de datos SQL de AzureBase de datos SQL en Microsoft Fabric

El término parámetros opcionales hace referencia a una variación específica del problema del plan sensible a parámetros (PSP) en el que el valor de parámetro confidencial que existe durante la ejecución de la consulta, controla si es necesario realizar una búsqueda en una tabla o examinarla. Un ejemplo sencillo sería algo parecido a:

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

En este ejemplo, SQL Server siempre elige un plan que examina la tabla Table1, incluso si hay un índice en Table1(col1). Es posible que un plan de búsqueda no pueda realizarse con nulos. Es posible que las técnicas de sugerencias de consulta, como OPTIMIZE FOR, no sean útiles para este tipo de problema de PSP porque actualmente no hay un operador que cambie dinámicamente una búsqueda de índice en un examen durante la ejecución. Es posible que este tipo de combinación de búsqueda y escaneo en tiempo de ejecución tampoco sea eficaz, ya que probablemente las estimaciones de cardinalidad de ese operador sean inexactas. El resultado es opciones de plan ineficaz y concesiones de memoria excesivas para consultas más complejas con patrones de consulta similares.

La característica de optimización del plan de parámetro opcional (OPPO) utiliza la infraestructura de optimización del plan adaptable (Multiplan) que se introdujo con la mejora de optimización del plan sensible a los parámetros, lo que genera varios planes a partir de una sola instrucción. Esto permite que la característica realice diferentes suposiciones en función de los valores de parámetro utilizados en la consulta. Durante el tiempo de ejecución de la consulta, OPPO selecciona el plan adecuado:

• donde el valor del parámetro IS NOT NULL usa un plan de búsqueda o algo más óptimo que un plan de escaneo completo.
• donde el valor del parámetro es NULL, usa un plan de examen.

Como parte de la familia de características de optimización de planes adaptables que incluye la optimización del plan sensible a parámetros, OPPO proporciona una solución al segundo componente del conjunto de características multiplan, que cubre las funcionalidades de búsqueda dinámica.

• Predicados de igualdad

  WHERE column1 = @p
  

• Búsqueda dinámica

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

Terminología y funcionamiento

Por ejemplo, considere un formulario web de solicitud para una empresa inmobiliaria que permita el filtrado opcional sobre el número de dormitorios en una lista específica. Un antipatrón común podría ser expresar el filtro opcional como:

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

Incluso si el parámetro @bedrooms = 10 se deduce mediante el uso de marcadores de parámetro y sabemos que es probable que la cardinalidad del número de dormitorios sea muy baja, el optimizador no genera un plan que busca en un índice que existe en la columna de dormitorios porque eso no es un plan válido para el caso donde @bedrooms es NULL. El plan generado no incluye un examen del índice.

Imagina si esto se pudiera reescribir como dos declaraciones independientes. Según el valor en tiempo de ejecución del parámetro , podríamos evaluar algo parecido a esto:

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

Esto se puede lograr mediante la infraestructura de optimización del plan adaptable, que permite la creación de un plan de despachador que despacha dos variantes de consulta.

Al igual que el intervalo de cardinalidad de predicado que usa la optimización de PSP, OPPO inserta una sugerencia de consulta utilizable por el sistema con el texto del plan de consulta. Esta sugerencia no es válida para su uso por parte de una aplicación o si intenta usarla usted mismo.

Siguiendo con el ejemplo anterior,

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

OPPO puede generar dos variantes de consulta que podrían tener los siguientes atributos agregados en el XML del plan de presentación:

• @bedrooms es NULL. La variante de consulta plegó la consulta original para lograr un plan de escaneo.

  SELECT * FROM Properties PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 1, optional_predicate(@bedrooms is NULL))

• @bedrooms IS NOT NULL

  SELECT * FROM Properties WHERE bedrooms = @bedrooms PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 2, optional_predicate(@bedrooms is NULL))

Uso de la optimización del plan de parámetros opcional

Para habilitar OPPO para una base de datos, se requieren los siguientes requisitos previos:

• La base de datos debe usar el nivel de compatibilidad 170.
• La OPTIONAL_PARAMETER_OPTIMIZATION configuración con ámbito de base de datos debe estar habilitada.

La OPTIONAL_PARAMETER_OPTIMIZATION configuración con ámbito de base de datos está habilitada de forma predeterminada. Esto significa que una base de datos que usa el nivel de compatibilidad 170 (el valor predeterminado en SQL Server 2025) usa OPPO de forma predeterminada.

Puede asegurarse de que una base de datos usa OPPO en SQL Server 2025 ejecutando las instrucciones siguientes:

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

Para deshabilitar la optimización de plan de parámetros opcionales para una base de datos, deshabilite la OPTIONAL_PARAMETER_OPTIMIZATION configuración a nivel de base de datos:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Uso de la optimización del plan de parámetros opcional mediante sugerencias de consulta

Puede usar la sugerencia de consulta DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION para deshabilitar la optimización del plan de parámetros opcionales para una consulta determinada. Las sugerencias deben especificarse a través de la USE HINT cláusula . Para obtener más información, consulte Sugerencias de consulta.

Las sugerencias funcionan en cualquier nivel de compatibilidad y anulan la OPTIONAL_PARAMETER_OPTIMIZATION configuración a nivel de base de datos.

La DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION sugerencia de consulta se puede especificar directamente en la consulta, ya sea a través de sugerencias del Almacén de Consultas.

Eventos extendidos

• optional_parameter_optimization_skipped_reason: se produce cuando OPPO decide que una consulta no es apta para la optimización. Este evento extendido sigue el mismo patrón que el evento parameter_sensitive_plan_optimization_skipped_reason que usa la optimización de PSP. Dado que una consulta puede generar variantes de consulta de PSP y OPPO, debe comprobar ambos eventos para comprender por qué una u otra característica fue activada. La siguiente consulta muestra todas las posibles razones por las que se omitió 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: El evento extendido sigue el mismo patrón que el evento query_with_parameter_sensitivity que se utiliza en la optimización de PSP. Incluye los campos adicionales presentes en las mejoras para la optimización de PSP, que consisten en mostrar el número de predicados que la función encontró interesantes, proporcionar más detalles en formato JSON sobre estos predicados interesantes, así como indicar si se admite OPPO para el predicado o los predicados.

Observaciones

• El XML ShowPlan para una variante de consulta tendría un aspecto similar al ejemplo siguiente, donde los predicados seleccionados tienen su información respectiva agregada al PLAN POR VALOR, indicador de predicado opcional.

<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">

• Salida de ejemplo del query_with_optional_parameter_predicate evento extendido

Contenido relacionado

• Guía de arquitectura de procesamiento de consultas
• Volver a compilar planes de ejecución
• Reutilización de parámetros y planes de ejecución
• Parametrización simple
• Parametrización forzada
• Sugerencias de consulta (Transact-SQL)
• Procesamiento de consultas inteligente en bases de datos SQL
• Sensibilidad de parámetros
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)