Compartir a través de

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

Aplica a: SQL Server 2025 (17.x) Azure SQL DatabaseSQL database in Microsoft Fabric

La optimización del plan de parámetros opcional (OPPO) mejora la calidad del plan de consulta para las consultas que incluyen parámetros opcionales. En estas consultas, el plan de ejecución óptimo depende de si un valor de parámetro está NULL en tiempo de ejecución. 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 del parámetro en tiempo de ejecución determina si la consulta requiere una búsqueda o un examen.

Información general

Las consultas que usan parámetros opcionales suelen incluir predicados que aplican filtros condicionalmente en función de si se proporciona un valor de parámetro. Un patrón común es el siguiente:

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

Cuando se produce @p IS NOT NULL, una búsqueda por índice en col1 suele ser el plan de ejecución más eficaz. Cuando @p IS NULL, el predicado se evalúa como TRUE y un examen podría ser más adecuado. Sin OPPO, el motor de base de datos de SQL Server debe compilar y almacenar en caché un único plan de ejecución válido para ambos casos. Dado que un plan basado en búsqueda no es válido cuando @p IS NULL, el optimizador suele elegir un plan basado en análisis conservador para todas las ejecuciones. Esta opción puede dar lugar a opciones de plan ineficaces y al uso excesivo de recursos para ejecuciones selectivas.

Las técnicas tradicionales de sugerencias como OPTIMIZE FOR no son eficaces en este escenario, ya que el plan debe permanecer correcto para ambos estados de parámetro.

OPPO usa la infraestructura de optimización del plan adaptable (Multiplan) introducida con la optimización del plan sensible a los parámetros (PSP). Esta infraestructura genera y almacena en caché varios planes de ejecución para una sola instrucción, lo que permite a OPPO realizar diferentes suposiciones en función de los valores de parámetro usados en la consulta.

Terminología y funcionamiento

OPPO se basa en el marco de optimización del plan adaptable (Multiplan), que también se utiliza en la optimización del plan sensible a parámetros. Mediante Multiplan, el motor de base de datos puede generar y almacenar en caché varios planes de ejecución para una sola consulta.

Cuando el motor de base de datos detecta un patrón de parámetro opcional apto, crea:

  • Un plan de despacho
  • Una o varias variantes de consulta, cada una optimizada para un estado de valor de parámetro específico

En tiempo de ejecución:

  • El motor de base de datos evalúa el valor del parámetro.
  • El despachador Multiplan selecciona la variante de consulta adecuada.
  • Se ejecuta la variante de consulta seleccionada.

Una vez que el motor de base de datos selecciona una variante de consulta, simplifica los predicados en función del valor del parámetro real. Tenga en cuenta la siguiente expresión:

@p1 IS NULL

En este ejemplo, la expresión se simplifica en un resultado constante para la variante seleccionada. Este plegamiento constante de resultados permite al optimizador generar planes de ejecución que no serían válidos en un único plan reutilizable.

Al seleccionar planes de esta manera, OPPO permite una ejecución eficaz para diferentes estados de parámetros sin necesidad de reescrituras de consultas ni sugerencias de consulta manuales.

La optimización de OPPO y PSP trata diferentes variaciones de problemas relacionados con los parámetros del plan.

  • La optimización de PSP selecciona planes en función de las diferencias de cardinalidad estimadas para predicados de igualdad o rango.

  • OPPO selecciona planes en función de si un valor de parámetro es NULL.

Una sola consulta puede beneficiarse de ambas características o en función de los predicados implicados.

Patrones de consulta admitidos

La optimización del plan de parámetros opcional se aplica a las consultas en las que NULL las comprobaciones en los parámetros afectan a la validez del plan de ejecución. 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. OPPO se aplica a predicados de parámetros opcionales disjuntivos, como:

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

Incluso si los marcadores de parámetros pueden examinar el @bedrooms = 10 parámetro y saben 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, ya que no es un plan válido para el caso en que @bedrooms sea NULL. El plan generado no incluye un examen del índice.

Imagínate si pudieras reescribir esta consulta como dos instrucciones independientes. En función del valor en tiempo de ejecución del parámetro, puede evaluar el ejemplo siguiente:

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

La característica puede lograrlo mediante la infraestructura multiplan, que permite crear un plan de distribuidor que envía una variante de consulta.

OPPO inserta una sugerencia de consulta generada por PLAN PER VALUE el sistema (optional_predicate) en los metadatos del plan para asociar cada variante de consulta con su estado de parámetro. Esta sugerencia es generada por el sistema e integrada dentro del texto de consulta del plan. Esta sugerencia no es válida para que la use una aplicación o que se aplique manualmente.

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 plega los predicados en función del valor del parámetro, lo que permite generar un plan basado en análisis.

    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)) -- Este código SQL selecciona todas las propiedades donde el número de dormitorios es el especificado, con un plan por valor para el ID de objeto y variante de consulta determinados, y un predicado opcional que verifica si los dormitorios son nulos.

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, por lo que una base de datos que usa el nivel de compatibilidad 170 (el valor predeterminado en SQL Server 2025 (17.x)) usa OPPO de forma predeterminada.

Puede asegurarse de que una base de datos usa OPPO en SQL Server 2025 (17.x) ejecutando las instrucciones siguientes:

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

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Para deshabilitar OPPO para una base de datos, deshabilite la OPTIONAL_PARAMETER_OPTIMIZATION configuración con ámbito de base de datos:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

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

Utilice la DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION sugerencia de consulta para deshabilitar OPPO para una consulta determinada. Especifique la sugerencia a través de la cláusula USE HINT. Para obtener más información, consulte Sugerencias de consulta.

Esta sugerencia funciona en cualquier nivel de compatibilidad e invalida la OPTIONAL_PARAMETER_OPTIMIZATION configuración con ámbito de base de datos.

Especifique la DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION sugerencia de consulta directamente en la consulta o a través de las sugerencias del Almacén de consultas.

Eventos extendidos

Use los siguientes eventos extendidos para solucionar problemas y diagnósticos. Estos eventos no son necesarios para usar la característica.

  • 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 usado por la optimización de PSP. Dado que una consulta puede generar tanto la optimización de PSP como las variantes de consulta de OPPO, verifique ambos eventos para comprender por qué se activó una característica o ninguna de ellas.

    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: este evento extendido sigue el mismo patrón que el evento que usa la query_with_parameter_sensitivity optimización de PSP. Incluye los campos adicionales que están disponibles en las mejoras para la optimización de PSP.

    Estos campos muestran:

    • el número de predicados que la funcionalidad encontró interesante,
    • más detalles sobre los predicados interesantes en formato JSON y
    • si OPPO es compatible con el predicado o los predicados.

Remarks

  • ShowPlan XML para una variante de consulta es similar al ejemplo siguiente. Los predicados que la función selecciona tienen su información correspondiente agregada a la sugerencia PLAN PER VALUEoptional_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">

  • Salida de ejemplo del query_with_optional_parameter_predicate evento extendido:

    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

Limitaciones y elegibilidad de las consultas

OPPO solo se aplica a las consultas que son aptas para la optimización multiplan. La característica no se aplica en escenarios que incluyen:

  • Consultas que usan variables locales en lugar de parámetros
  • Consultas compiladas con OPTION (RECOMPILE)
  • Consultas ejecutadas con SET ANSI_NULLS OFF
  • Declaraciones parametrizadas automáticamente

Contenido relacionado

  • Last updated on