Partager via

Optimisation facultative du plan de paramètres (OPPO)

S’applique à : SQL Server 2025 (17.x) Azure SQL DatabaseSQL database in Microsoft Fabric

L’optimisation facultative du plan de paramètres (OPPO) améliore la qualité du plan de requête pour les requêtes qui incluent des paramètres facultatifs. Dans ces requêtes, le plan d’exécution optimal varie selon qu’une valeur de paramètre est NULL au moment de l’exécution. Le terme paramètres facultatifs fait référence à une variante spécifique du problème de plan sensible aux paramètres (PSP), dans laquelle la valeur du paramètre au moment de l’exécution détermine si la requête nécessite une recherche ou une analyse.

Aperçu

Les requêtes qui utilisent des paramètres facultatifs incluent souvent des prédicats qui appliquent des filtres de manière conditionnelle selon qu’une valeur de paramètre est fournie. Un modèle courant est le suivant :

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

Quand @p IS NOT NULL, une recherche d’index sur col1 est souvent le plan d’exécution le plus efficace. Quand @p IS NULL, le prédicat est évalué à TRUE, et une analyse séquentielle peut être plus appropriée. Sans OPPO, le moteur de base de données SQL Server doit compiler et mettre en cache un plan d’exécution unique valide pour les deux cas. Étant donné qu’un plan basé sur la recherche n’est pas valide quand @p IS NULL, l’optimiseur choisit souvent un plan conservateur basé sur l’analyse pour toutes les exécutions. Ce choix peut entraîner des choix de plan inefficaces et une utilisation excessive des ressources pour les exécutions sélectives.

Les techniques d’indication traditionnelles telles que OPTIMIZE FOR ne sont pas efficaces dans ce scénario, car le plan doit rester correct pour les deux états de paramètre.

OPPO utilise l’infrastructure d’optimisation de plan adaptatif (Multiplan) introduite avec l’optimisation du plan sensible aux paramètres (PSP). Cette infrastructure génère et met en cache plusieurs plans d’exécution pour une instruction unique, ce qui permet à OPPO d’effectuer différentes hypothèses en fonction des valeurs de paramètre utilisées dans la requête.

Terminologie et fonctionnement

OPPO s’appuie sur l’infrastructure d’optimisation de plan adaptatif (Multiplan), qui est également utilisée par l’optimisation de plan sensible aux paramètres. À l’aide de Multiplan, le moteur de base de données peut générer et mettre en cache plusieurs plans d’exécution pour une requête unique.

Lorsque le moteur de base de données détecte un modèle de paramètre facultatif éligible, il crée :

  • Un plan de gestionnaire de tâches
  • Une ou plusieurs variantes de requête, chacune optimisée pour un état de valeur de paramètre spécifique

Au moment de l’exécution :

  • Le moteur de base de données évalue la valeur du paramètre.
  • Le répartiteur Multiplan sélectionne la variante de requête appropriée.
  • La variante de requête sélectionnée s’exécute.

Une fois que le moteur de base de données sélectionne une variante de requête, il simplifie les prédicats en fonction de la valeur de paramètre réelle. Considérez l’expression suivante :

@p1 IS NULL

Dans cet exemple, l’expression est simplifiée en résultat constant pour la variante sélectionnée. Ce repli de résultat constant permet à l’optimiseur de générer des plans d’exécution qui ne sont pas valides dans un plan réutilisable unique.

En sélectionnant des plans de cette façon, OPPO permet une exécution efficace pour différents états de paramètres sans nécessiter de réécritures de requête ou d’indicateurs de requête manuels.

L’optimisation OPPO et PSP traite différentes variantes de problèmes liés aux paramètres dans les plans.

  • L’optimisation PSP sélectionne des plans en fonction des différences de cardinalité estimées pour les prédicats d’égalité ou de plage.

  • OPPO sélectionne des plans en fonction de la valeur NULLd’un paramètre.

Une requête unique peut tirer parti soit des deux fonctionnalités, soit d'une seule, en fonction des prédicats impliqués.

Modèles de requête pris en charge

L’optimisation facultative du plan de paramètres s’applique aux requêtes où NULL les vérifications sur les paramètres affectent la validité du plan d’exécution. Par exemple, considérez un formulaire en ligne d'une application pour une société immobilière qui permet un filtrage facultatif sur le nombre de chambres à coucher pour une annonce particulière. OPPO s’applique aux prédicats de paramètres facultatifs disjonctifs tels que :

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

Même si les marqueurs de paramètres peuvent sniffer le @bedrooms = 10 paramètre, et que vous savez que la cardinalité pour le nombre de chambres est susceptible d’être très faible, l’optimiseur ne produit pas de plan qui recherche sur un index qui existe sur la colonne de chambre, car ce n’est pas un plan valide pour le cas où @bedrooms est NULL. Le plan généré n’inclut pas d’analyse de l’index.

Imaginez si vous pouvez réécrire cette requête sous la forme de deux instructions distinctes. Selon la valeur d’exécution du paramètre, vous pouvez évaluer l’exemple suivant :

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

La fonctionnalité peut y parvenir à l’aide de l’infrastructure Multiplan, qui permet la création d’un plan de répartiteur qui répartit une variante de requête.

OPPO incorpore un indicateur de requête généré par le système PLAN PER VALUE (optional_predicate) dans les métadonnées du plan pour associer chaque variante de requête à son état des paramètres. Cet indicateur est généré par le système et incorporé dans le texte de requête du plan. Cet indicateur n’est pas valide pour une utilisation par une application ou pour être appliqué manuellement.

En continuant avec l’exemple précédent,

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

OPPO peut générer deux variantes de requête qui peuvent avoir les attributs suivants ajoutés dans le code XML showplan :

  • @bedrooms a la valeur NULL. La variante de requête plie les prédicats en fonction de la valeur du paramètre, ce qui permet de générer un plan basé sur l’analyse.

    SELECT * FROM Properties PLAN PAR VALEUR(ObjectID = 1234, QueryVariantID = 1, optional_predicate(@bedrooms est NULL))

  • @bedrooms IS NOT NULL

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

Utiliser l’optimisation facultative du plan de paramètres

Pour activer OPPO pour une base de données, les conditions préalables suivantes sont requises :

  • La base de données doit utiliser le niveau de compatibilité 170.
  • La OPTIONAL_PARAMETER_OPTIMIZATION configuration configurée au niveau de la base de données doit être activée.

La OPTIONAL_PARAMETER_OPTIMIZATION configuration étendue à la base de données est activée par défaut. Par conséquent, une base de données utilisant le niveau de compatibilité 170 (la valeur par défaut dans SQL Server 2025 (17.x)) utilise OPPO par défaut.

Vous pouvez vous assurer qu’une base de données utilise OPPO dans SQL Server 2025 (17.x) en exécutant les instructions suivantes :

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

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Pour désactiver OPPO pour une base de données, désactivez la configuration délimitée à la OPTIONAL_PARAMETER_OPTIMIZATION base de données :

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Utiliser l’optimisation facultative du plan de paramètres via l’indicateur de requête

Utilisez l'indice de requête DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION pour désactiver OPPO pour une requête spécifique. Spécifiez l’indicateur via la USE HINT clause. Pour plus d’informations, consultez indicateurs de requête.

Cet indicateur fonctionne sous n’importe quel niveau de compatibilité et remplace la configuration délimitée par la OPTIONAL_PARAMETER_OPTIMIZATION base de données.

Spécifiez l’indicateur DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION de requête directement dans la requête ou via les indicateurs de Query Store.

Événements étendus

Utilisez les événements étendus suivants pour la résolution des problèmes et les diagnostics. Ces événements ne sont pas nécessaires pour utiliser la fonctionnalité.

  • optional_parameter_optimization_skipped_reason: se produit lorsque OPPO décide qu’une requête n’est pas éligible à l’optimisation. Cet événement étendu suit le même modèle que celui utilisé par l'optimisation PSP pour l'événement parameter_sensitive_plan_optimization_skipped_reason. Étant donné qu’une requête peut générer des variantes de requête PSP et OPPO, vérifiez les deux événements pour comprendre pourquoi une ou aucune fonction n’est activée.

    La requête suivante montre toutes les raisons possibles pour lesquelles le PSP a été ignoré :

    SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = 'opo_skipped_reason_enum'
ORDER BY map_key;

  • query_with_optional_parameter_predicate : Cet événement étendu suit le même modèle que celui utilisé par l'optimisation PSP pour l'événement query_with_parameter_sensitivity. Il inclut les champs supplémentaires disponibles dans les améliorations apportées à l’optimisation PSP.

    Ces champs s’affichent :

    • le nombre de prédicats que la fonctionnalité a trouvé intéressant,
    • plus d’informations au format JSON concernant les prédicats intéressants et
    • si OPPO est pris en charge pour le prédicat ou les prédicats.

Remarks

  • ShowPlan XML pour une variante de requête ressemble à l’exemple suivant. Les prédicats sélectionnés par la fonctionnalité ont leurs informations respectives ajoutées à l’indicateur 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">

  • Exemple de sortie de l’événement query_with_optional_parameter_predicate étendu :

    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

Éligibilité et limitations des requêtes

OPPO s’applique uniquement aux requêtes éligibles à l’optimisation Multiplan. La fonctionnalité n’est pas appliquée dans les scénarios suivants :

  • Requêtes qui utilisent des variables locales au lieu de paramètres
  • Requêtes compilées avec OPTION (RECOMPILE)
  • Requêtes exécutées avec SET ANSI_NULLS OFF
  • Instructions paramétrables automatiquement

Contenu connexe

  • Last updated on