Optimisation facultative du plan de paramètres (OPPO)

S’applique à :S’applique à : Préversion de SQL Server 2025 (17.x) Azure SQL DatabaseBase de données SQL dans Microsoft Fabric Préversion

Le terme paramètres facultatifs fait référence à une variante spécifique du problème de plan sensible au paramètre (PSP) dans lequel la valeur sensible, de paramètre qui existe pendant l’exécution de la requête, contrôle si nous devons effectuer une recherche dans ou analyser une table. Voici un exemple simple :

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

Dans cet exemple, SQL Server choisit toujours un plan qui analyse la table Table1, même s’il existe un index sur Table1(col1). Un plan de recherche peut ne pas être possible avec des NULL. Les techniques d’indication de requête, comme OPTIMIZE FOR, peuvent ne pas être utiles pour ce type de problème PSP, car il n’existe actuellement pas d’opérateur qui modifie dynamiquement une recherche d’index en balayage pendant l’exécution. Ce type de combinaison de recherche-balayage> lors de l'exécution peut également ne pas être efficace, car les estimations de cardinalité associées à cet opérateur seraient probablement inexactes. Le résultat est des choix de plan inefficaces et des allocations de mémoire excessives pour les requêtes plus complexes avec des modèles de requête similaires.

La fonctionnalité d’optimisation facultative du plan de paramètres (OPPO) utilise l’infrastructure d’optimisation de plan adaptatif (Multiplan) introduite avec l’amélioration de l’optimisation du plan sensible aux paramètres, qui génère plusieurs plans à partir d’une seule instruction. Cela permet à la fonctionnalité d’effectuer différentes hypothèses en fonction des valeurs de paramètre utilisées dans la requête. Pendant l’exécution de la requête, OPPO sélectionne le plan approprié :

• lorsque la valeur du paramètre est IS NOT NULL, il utilise un plan de recherche ou une méthode plus optimale qu’un plan d’analyse complète.
• où la valeur du paramètre est NULL, elle utilise un plan d’analyse.

Dans le cadre de la famille de fonctionnalités d’optimisation de plan adaptatif qui inclut l’optimisation du plan sensible aux paramètres, OPPO fournit une solution au deuxième composant de l’ensemble de fonctionnalités Multiplan, qui couvre les fonctionnalités de recherche dynamique.

• Equality predicates

  WHERE column1 = @p
  

• Dynamic search

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

Terminologie et fonctionnement

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. Un antimodèle courant peut être d’exprimer le filtre facultatif comme suit :

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

Même si le paramètre @bedrooms = 10 est sniffé par l’utilisation de marqueurs de paramètres, et nous savons que la cardinalité pour le nombre de chambres à coucher 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 cela peut être réécrit sous la forme de deux instructions distinctes. En fonction de la valeur d’exécution du paramètre, nous pouvons évaluer quelque chose comme suit :

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

Pour ce faire, nous pouvons utiliser l’infrastructure d’optimisation de plan adaptatif, ce qui permet de créer un plan de répartiteur qui répartit deux variantes de requête.

Comme pour la plage de cardinalité de prédicat utilisée par l’optimisation PSP, OPPO intègre un indice de requête exploitable par le système dans le texte de requête du plan. Cet indicateur n’est pas valide pour une utilisation par une application ou si vous tentez de l’utiliser vous-même.

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 a plié la requête d’origine pour obtenir un plan d’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 configuration ayant pour périmètre la base de données est activée par défaut. Cela signifie qu’une base de données utilisant le niveau de compatibilité 170 (valeur par défaut dans SQL Server 2025) utilise OPPO par défaut.

Vous pouvez vous assurer qu’une base de données utilise OPPO dans SQL Server 2025 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 l'optimisation facultative du plan de paramètres pour une base de données, désactivez la configuration étendue à l'ensemble de la base de données :

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

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

Vous pouvez utiliser l’indicateur DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION de requête pour désactiver l’optimisation facultative du plan de paramètres pour une requête donnée. Les indicateurs doivent être spécifiés via la USE HINT clause. Pour plus d’informations, consultez indicateurs de requête.

Les indicateurs fonctionnent sous n’importe quel niveau de compatibilité et remplacent la configuration délimitée par la OPTIONAL_PARAMETER_OPTIMIZATION base de données.

L’indicateur DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION de requête peut être spécifié directement dans la requête ou via des indicateurs du Magasin des requêtes.

Extended Events

• 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 l’événement parameter_sensitive_plan_optimization_skipped_reason utilisé par l’optimisation PSP. Étant donné qu'une requête peut générer à la fois des variantes d'optimisation de requête PSP et OPPO, vous devez vérifier les deux variantes pour comprendre pourquoi l'une ou aucune fonctionnalité 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: l’événement étendu suit le même modèle que l’événement query_with_parameter_sensitivity utilisé par l’optimisation PSP. Il inclut les champs supplémentaires disponibles dans les améliorations de l'optimisation PSP, qui consistent à afficher le nombre de prédicats que la fonctionnalité juge intéressants, fournir plus de détails au format json concernant ces prédicats intéressants, ainsi que préciser si OPPO est pris en charge pour le ou les prédicats.

Remarks

• Le code XML ShowPlan d’une variante de requête ressemble à l’exemple suivant, où les prédicats sélectionnés ont leurs informations respectives ajoutées au PLAN PER VALUE, indicateur 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

Related content

• Guide d’architecture de traitement des requêtes
• Recompilation des plans d’exécution
• Paramètres et réutilisation du plan d’exécution
• Simple parameterization
• Forced parameterization
• Indicateurs de requête (Transact-SQL)
• Traitement de requêtes intelligent dans les bases de données SQL
• Parameter Sensitivity
• MODIFIER LA CONFIGURATION DE L’ÉTENDUE DE LA BASE DE DONNÉES (Transact-SQL)