Optimisation des plans sensibles aux paramètres

S’applique à : SQL Server 2022 (16.x) et versions ultérieures Base de données SQL AzureInstance gérée SQL AzureBase de données SQL Azure dans Microsoft Fabric

L’optimisation du plan de sensibilité des paramètres (PSP) fait partie de la famille de fonctionnalités de traitement intelligent des requêtes. Elle traite le scénario dans lequel un seul plan mis en cache pour une requête paramétrable n’est pas optimal pour toutes les valeurs de paramètre entrantes possibles. C’est le cas avec les distributions de données non uniformes. Pour plus d’informations, consultez Sensibilité des paramètres et Réutilisation des paramètres et du plan d’exécution.

Pour plus d’informations sur les solutions de contournement existantes pour ce scénario de problème, consultez :

• Examinez et résolvez les problèmes de sensibilité aux paramètres
• Réutilisation des paramètres et des plans d’exécution
• Requêtes confrontées à des problèmes de plans d'exécution sensibles aux paramètres

L'optimisation des PSP active automatiquement plusieurs plans mis en cache actifs pour une instruction paramétrée unique. Les plans d’exécution mis en cache prennent en charge différentes tailles de données en fonction de la ou des valeurs de paramètre d’exécution fournies par le client.

Comprendre le paramétrage

Dans le moteur de base de données SQL Server, l’utilisation de paramètres ou de marqueurs de paramètres dans les instructions T-SQL augmente la capacité du moteur relationnel à associer les nouvelles instructions T-SQL aux plans d’exécution préalablement compilés existants et à promouvoir la réutilisation des plans. Pour plus d’informations, consultez Paramétrisation simple.

Vous pouvez remplacer le comportement de la paramétrisation simple par défaut de SQL Server en spécifiant que toutes les instructions SELECT, INSERT, UPDATE et DELETE dans une base de données sont paramétrables dans certaines limites. Pour plus d'informations, consultez Paramétrage forcé.

Implémentation de l'optimisation PSP

Pendant la compilation initiale, les histogrammes de statistiques de colonne identifient les distributions non uniformes et évaluent les prédicats paramétrables les plus à risque, dans la limite de trois parmi tous les prédicats disponibles. En d’autres termes, si plusieurs prédicats dans la même requête répondent aux critères, l’optimisation PSP choisit les trois premiers. La fonctionnalité PSP limite le nombre de prédicats évalués, afin d’éviter d’encombrer le cache de plans et le Magasin des requêtes (si le Magasin des requêtes est activé) avec un trop grand nombre de plans.

Pour les plans éligibles, la compilation initiale produit un plan de répartiteur qui contient la logique d’optimisation de type PSP, appelée expression de répartiteur. Un plan de répartiteur est mappé aux variantes de requête en fonction des prédicats des valeurs limites de plage de cardinalité.

Terminology

Gestionnaire d'expressions

Évalue la cardinalité des prédicats en fonction des valeurs des paramètres d’exécution et route l’exécution vers des variantes différentes des requêtes.

Plan de répartiteur

Un plan qui contient l'expression du répartiteur est mis en cache pour la requête originale. Le plan de répartiteur est essentiellement une collection des prédicats qui ont été sélectionnés par la fonctionnalité avec quelques détails supplémentaires. Pour chaque prédicat sélectionné, certains des détails inclus dans le plan de répartiteur sont les valeurs des limites haute et basse. Ces valeurs sont utilisées pour diviser les valeurs des paramètres en différents compartiments ou plages. Le plan de répartiteur contient également les statistiques utilisées pour calculer les valeurs limites.

Variant de requête

Comme un plan de dispatch évalue la cardinalité des prédicats en fonction des valeurs des paramètres d’exécution, il catégorise ces valeurs et ensuite génère des requêtes enfants distinctes à compiler et à exécuter. Ces requêtes secondaires sont appelées variantes de requêtes. Les variantes de requête ont leurs propres plans dans le cache du plan et dans le Magasin des requêtes.

Plage de cardinalité de prédicat

Au moment de l’exécution, la cardinalité de chaque prédicat est évaluée en fonction des valeurs des paramètres d’exécution. Le répartiteur compartimente les valeurs de cardinalité dans trois plages de cardinalité de prédicat au moment de la compilation. Par exemple, la fonctionnalité d’optimisation PSP peut créer trois plages qui vont représenter des plages de cardinalité faible, moyenne et élevée, comme illustré dans le diagramme suivant.

En d’autres termes, quand une requête paramétrable est compilée initialement, la fonctionnalité d’optimisation PSP génère un plan de shell appelé plan de répartiteur. L’expression de répartiteur a la logique qui compartimente les requêtes dans des variantes de requête en fonction des valeurs des paramètres à l’exécution. Quand l’exécution réelle commence, le répartiteur effectue deux étapes :

• le répartiteur évalue son expression de répartiteur pour l’ensemble donné de paramètres pour calculer la plage de cardinalité.

• le répartiteur mappe ces plages à des variantes de requête spécifiques, puis compile et exécute les variantes. En raison de la présence de plusieurs variantes de requête, la fonctionnalité d’optimisation PSP permet d’avoir plusieurs plans pour une même requête.

Les limites de plage de cardinalité sont visibles dans le code XML ShowPlan d’un plan de répartition :

<Dispatcher>
  <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1000000">
    <StatisticsInfo Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Statistics="[NCI_Property_AgentId]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-09-07T15:32:16.89" />
    <Predicate>
      <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="@AgentId" />
            </Identifier>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Predicate>
  </ParameterSensitivePredicate>
</Dispatcher>

Un indice généré par l'optimisation PSP est ajouté à l'instruction SQL dans le ShowPlan XML d'une variante de requête. L’indicateur ne peut pas être utilisé directement et n’est pas analysé s’il est ajouté manuellement. L’indicateur comporte les éléments suivants :

option ( PLAN PER VALUE ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [databaseName].[ schemaName]. [tableName]. [columnName] = @paramName, lowBoundaryValue, highBoundaryValue ) )

• ObjectID provient du module (autrement dit, de la procédure stockée, de la fonction, du déclencheur) dont l’instruction actuelle fait partie ; avec l’hypothèse que l’instruction a été générée à partir d’un module. Si l’instruction est le résultat de SQL dynamique ou ad hoc (autrement dit, sp_executesql) l’élément ObjectID est égal à 0.
• QueryVariantID équivaut approximativement à la combinaison de plages pour tous les prédicats sélectionnés par l’optimisation PSP. Par exemple, si une requête a deux prédicats éligibles pour PSP et que chaque prédicat a trois plages, il y aura neuf plages de variantes de requête numérotées 1 à 9.
• la plage de prédicats est les informations de plage de cardinalité générées à partir de l’expression du répartiteur.

Et dans le fichier XML ShowPlan d’une variante de requête (à l’intérieur de l’élément Dispatcher) :

<Batch>
  <Statements>
    <StmtSimple StatementText="SELECT PropertyId,&#xD;&#xA;       AgentId,&#xD;&#xA;       MLSLinkId,&#xD;&#xA;       ListingPrice,&#xD;&#xA;       ZipCode,&#xD;&#xA;       Bedrooms,&#xD;&#xA;       Bathrooms&#xD;&#xA;FROM dbo.Property&#xD;&#xA;WHERE AgentId = @AgentId&#xD;&#xA;ORDER BY ListingPrice DESC option (PLAN PER VALUE(ObjectID = 613577224, QueryVariantID = 1, predicate_range([PropertyMLS].[dbo].[Property].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090085E4372DFC69BB9E7EBA421561DE8E1E0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="0.021738" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x476167A000F589CD" QueryPlanHash="0xDE982107B7C28AAE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160">
      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

      <Dispatcher>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2019-09-07T15:32:16.89" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_Property_AgentId]" Table="[Property]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
      </Dispatcher>

    </StmtSimple>
  </Statements>
</Batch>

Remarks

• À compter de SQL Server 2025 (17.x) et de la compatibilité de base de données 170, l’optimisation PSP comprend les quatre améliorations suivantes :

  • Prise en charge des instructions DML (Data Manipulation Language) de langage de manipulation de données telles que DELETE, INSERT, MERGE et UPDATE.
  • Prise en charge étendue pour tempdb.
  • Considérations supplémentaires prises en compte dans les scénarios où plusieurs prédicats éligibles existent sur la même table.
  • Modifications apportées à l’événement query_with_parameter_sensitivity étendu qui comprenait les champs interesting_predicate_count, max_skewness, psp_optimization_supported et query_type avant les modifications apportées à SQL Server 2025 (17.x) et à la compatibilité de la base de données 170. Mais incluez maintenant les champs interesting_predicate_count, interesting_predicate_details, psp_optimization_supported et query_type. Pour plus d’informations, consultez la section Événements étendus .

• La fonctionnalité d’optimisation du PSP fonctionne actuellement uniquement avec les prédicats d’égalité.

• Les plans de répartiteur sont automatiquement reconstruits s’il existe des modifications importantes de la distribution des données. Les plans de variantes de requête se recompilent indépendamment en fonction des besoins, à l’image de tout autre type de plan de requête, sous réserve des événements de recompilation par défaut. Pour plus d’informations sur la recompilation, consultez Recompilation des plans d’exécution.

• La vue du catalogue système sys.query_store_plan du Magasin des requêtes a été modifiée pour différencier un plan compilé normal, un plan de répartiteur et un plan de variante de requête. La nouvelle vue du catalogue système de Query Store, sys.query_store_query_variant, contient des informations sur les relations parent-enfant entre les requêtes paramétrées d'origine (également appelées requêtes parentes), les plans de répartiteur et leurs variantes de requête enfants.

• Lorsqu’il existe plusieurs prédicats qui font partie de la même table, l’optimisation du PSP sélectionne le prédicat qui a le plus d’asymétrie des données en fonction de l’histogramme des statistiques sous-jacents. Par exemple, avec SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2, car les deux column1 = @predicate1 et column2 = @predicate2 sont à partir de la même table, table1, seul le prédicat le plus asymétrique sera évalué par la fonctionnalité. Toutefois, si l’exemple de requête implique un opérateur tel qu’un UNION, le PSP évalue plusieurs prédicats. Par exemple, si une requête a des caractéristiques similaires à SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicate, le PSP choisit au plus deux prédicats dans ce cas, car le système traite ce scénario comme s’il s’agit de deux tables différentes. Le même comportement peut être observé à partir de requêtes qui se joignent automatiquement via des alias de table.

• Le code XML ShowPlan d’une variante de requête ressemble à l’exemple suivant, où les deux prédicats sélectionnés ont leurs informations respectives ajoutées à l’indicateur associé PLAN PER VALUE PSP.

  <Batch>
    <Statements>
      <StmtSimple StatementText="SELECT  b.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty a join  PropertyDetails b on a.PropertyId = b.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id&#xD;&#xA;UNION&#xD;&#xA;          SELECT  c.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty c join  PropertyDetails d on c.PropertyId = d.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id option (PLAN PER VALUE(ObjectID = 981578535, QueryVariantID = 9, predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0),predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090051FBCD918F8DFD60D324887356B422D90000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="29.2419" StatementEstRows="211837" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6D2A4E407085C01E" QueryPlanHash="0x72101C0A0DD861AB" CardinalityEstimationModelVersion="160" BatchModeOnRowStoreUsed="true">
        <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
        <Dispatcher>
          <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
            <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
            <Predicate>
              <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [a].[AgentId]=[@AgentId]">
                <Compare CompareOp="EQ">
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[a]" Column="AgentId" />
                    </Identifier>
                  </ScalarOperator>
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Column="@AgentId" />
                    </Identifier>
                  </ScalarOperator>
                </Compare>
              </ScalarOperator>
            </Predicate>
          </ParameterSensitivePredicate>
          <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
            <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
            <Predicate>
              <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [c].[AgentId]=[@AgentId]">
                <Compare CompareOp="EQ">
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[c]" Column="AgentId" />
                    </Identifier>
                  </ScalarOperator>
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Column="@AgentId" />
                    </Identifier>
                  </ScalarOperator>
                </Compare>
              </ScalarOperator>
            </Predicate>
          </ParameterSensitivePredicate>
        </Dispatcher>
        <QueryPlan CachedPlanSize="160" CompileTime="17" CompileCPU="17" CompileMemory="1856" QueryVariantID="9">
  

• Vous pouvez influencer les seuils d’asymétrie actuels utilisés par la fonctionnalité d’optimisation du PSP, avec une ou plusieurs des méthodes suivantes :

  • Indicateurs de trace de l’estimateur de cardinalité (CE), tels que l’indicateur de trace 9481 (global, session ou niveau de requête)

  • Options de configuration dans la portée de la base de données qui tentent de réduire le modèle CE utilisé ou d'influencer les hypothèses que le modèle CE formule en ce qui concerne l’indépendance de plusieurs prédicats. Cela est particulièrement utile dans les cas où les statistiques multi-colonnes n’existent pas, ce qui affecte la capacité de l'optimisation PSP à évaluer la pertinence de ces prédicats.

  • Pour plus d’informations, consultez la section Augmentation de l’hypothèse de corrélation pour plusieurs prédicats du livre blanc Optimisez vos plans de requête avec l’estimateur de cardinalité SQL Server 2014. Le modèle CE plus récent tente de supposer une corrélation et moins d’indépendance pour la conjonction et la disjonction des prédicats. L'utilisation de l'ancien modèle CE peut affecter la manière dont la sélectivité des prédicats dans un scénario de jointure à plusieurs colonnes est calculée. Cette action ne doit être prise en compte que pour des scénarios spécifiques, et il n’est pas recommandé d’utiliser le modèle CE hérité pour la plupart des charges de travail.

• L’optimisation PSP compile et exécute actuellement chaque variante de requête en tant qu’instruction préparée, qui est l’une des raisons pour lesquelles les variantes de requête perdent leur association avec les object_id des modules parents si le plan de répartiteur était basé sur un module (c’est-à-dire une procédure stockée, un déclencheur, une fonction, une vue, etc.). En tant qu’instruction préparée, le object_id ne peut pas être mappé directement à un objet dans sys.objects, mais il s’agit essentiellement d’une valeur calculée basée sur un hachage interne du texte du lot. Pour plus d’informations, consultez la section Table Returned de la documentation sys.dm_exec_plan_attributes DMV.

  Les plans variants de requête sont placés dans le magasin d’objets du cache de plan (CACHESTORE_OBJCP) tandis que les plans de répartition sont placés dans le magasin de cache des plans SQL (CACHESTORE_SQLCP). Toutefois, la fonctionnalité PSP stocke le object_id d’un parent d’une variante de requête dans l’attribut ObjectID qui fait partie de l'indice PLAN PER VALUE que la fonctionnalité PSP intègre dans le XML ShowPlan si la requête parente fait partie d’un module et n’est pas une requête T-SQL dynamique ou ad hoc. Les statistiques de performances agrégées pour les procédures, fonctions et déclencheurs mis en cache peuvent continuer à être utilisées à des fins respectives. Toutefois, les statistiques liées à l’exécution plus granulaire, telles que celles trouvées dans les vues similaires à la DMV sys.dm_exec_query_stats, contiennent toujours des données pour les variantes de requête, mais l’association entre les object_id pour les variantes de requête et les objets de la table sys.objects n’est pas alignée, sans traitement supplémentaire du code XML ShowPlan pour chacune des variantes de requête dans lesquelles des statistiques d’exécution plus granulaires sont requises. Les informations de statistiques d’exécution et d’attente pour les variantes de requête peuvent être obtenues à partir du Magasin des requêtes sans techniques d’analyse XML ShowPlan supplémentaires si le Magasin des requêtes est activé.

• Étant donné que les variantes de requête PSP sont exécutées en tant que nouvelle instruction préparée, le object_id n'est pas automatiquement exposé dans les différents DMV associés au cache de plan sys.dm_exec_* sans fragmenter le code XML ShowPlan et appliquer des techniques de correspondance de modèle de texte, c'est-à-dire un traitement XQuery supplémentaire. Seuls les plans d'optimisation de répartiteur PSP émettent actuellement l'ID d'objet parent approprié. Le object_id est exposé dans le Magasin des requêtes, car le Magasin des requêtes permet un modèle plus relationnel que la hiérarchie du plan cache. Pour plus d’informations, consultez la vue du catalogue système du Store de requêtes sys.query_store_query_variant.

Considerations

• Pour activer l’optimisation PSP, activez le niveau de compatibilité de base de données 160 pour la base de données à laquelle vous vous connectez lors de l’exécution de la requête.

• Pour obtenir des informations supplémentaires sur la fonctionnalité d'optimisation PSP, nous recommandons d'activer l'intégration du Magasin de requêtes en l'activant. L’exemple suivant active le Magasin des requêtes pour une base de données préexistante appelée MyNewDatabase:

ALTER DATABASE [MyNewDatabase]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO
);

Note

À compter de SQL Server 2022 (16.x), le Magasin des requêtes est activé par défaut pour les bases de données nouvellement créées.

• Pour désactiver l'optimisation PSP au niveau de la base de données, utilisez la configuration de portée de base de données ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF.

• Pour désactiver l'optimisation PSP au niveau de la requête, utilisez le hint de requête DISABLE_PARAMETER_SENSITIVE_PLAN.

• Si la détection de paramètre est désactivée par l’indicateur de trace 4136, la configuration à l’échelle de la base de données PARAMETER_SNIFFING ou l’indicateur de requête USE HINT('DISABLE_PARAMETER_SNIFFING'), l’optimisation PSP est désactivée pour les charges de travail et les contextes d’exécution associés. Pour plus d’informations, consultez indicateurs de requête et ALTER DATABASE SCOPED CONFIGURATION.

• Le nombre de variantes de plan uniques par dispatcheur stocké dans le cache du plan est limité pour éviter la surcharge du cache. Le seuil interne n’est pas documenté. Étant donné que chaque lot SQL peut créer plusieurs plans et que chaque plan de variante de requête a une entrée indépendante dans le cache du plan, il est possible d’atteindre le nombre maximal par défaut d’entrées de plan autorisées. Si le taux d’éviction du cache de plan est sensiblement élevé ou si les tailles des CACHESTORE_OBJCP et CACHESTORE_SQLCPmagasins de cache sont excessives, vous devez envisager d’appliquer le trace flag 174.

• Le nombre de variantes de plan uniques pouvant être stockées pour une requête dans le magasin des requêtes est limité par l’option de configuration max_plans_per_query. Comme les variantes de requête peuvent avoir plusieurs plans, un total de 200 plans peut être présent par requête dans le Magasin des requêtes. Ce nombre inclut tous les plans de variantes de requêtes pour tous les répartiteurs appartenant à une requête parente. Envisagez d’augmenter l’option de configuration du max_plans_per_query Query Store.

  • Un exemple de la façon dont le nombre de plans uniques peut dépasser la limite par défaut du magasin des requêtes max_plans_per_query est un scénario dans lequel vous observez le comportement suivant. Supposons que vous avez une requête avec un ID de requête de 10, qui a deux plans de répartiteur et chaque plan de répartiteur a 20 variantes de requête chacune (40 variantes de requête au total). Le nombre total de plans pour l’ID de requête 10 est de 40 plans pour les variantes de requête et les deux plans de répartiteur. Il est également possible que la requête parente elle-même (ID de requête 10) puisse avoir 5 plans réguliers (non-dispatcher). Cela fait 47 plans (40 à partir de variantes de requête, 2 répartiteurs et 5 plans non liés à PSP). En outre, si chaque variante de requête a en moyenne cinq plans, il est possible dans ce scénario d’avoir plus de 200 plans dans le Magasin de requêtes pour une requête principale. Cela dépend également du déséquilibre important des données dans le ou les jeux de données auxquels cet exemple de requête parente pourrait faire référence.

• Pour chaque mappage de variantes de requête à un répartiteur donné :

  • Le query_plan_hash est unique. Cette colonne est disponible dans sys.dm_exec_query_stats et dans d’autres vues de gestion dynamique et tables de catalogue.
  • Le plan_handle est unique. Cette colonne est disponible dans sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans et dans d’autres fonctions et vues de gestion dynamique et tables de catalogue.
  • Le query_hash étant commun à d’autres variantes mappant au même répartiteur, il est possible de déterminer l’utilisation agrégée des ressources pour les requêtes qui diffèrent uniquement par les valeurs de paramètre d’entrée. Cette colonne est disponible dans sys.dm_exec_query_stats, sys.query_store_query et dans d’autres vues de gestion dynamique et tables de catalogue.
  • Le sql_handle est unique car des identificateurs d'optimisation PSP (Plan de Sensibilité des Paramètres) spéciaux sont ajoutés au texte de la requête lors de la compilation. Cette colonne est disponible dans sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans et dans d’autres fonctions et vues de gestion dynamique et tables de catalogue. Les mêmes informations de handle que la colonne last_compile_batch_sql_handle de la table de catalogue sys.query_store_query sont disponibles dans le Magasin des requêtes.
  • Le query_id est unique dans le Query Store. Cette colonne est disponible dans sys.query_store_query et d'autres tables de catalogue du Query Store.

Forçage de plan dans le Store des requêtes

Utilise les mêmes procédures stockées sp_query_store_force_plan et sp_query_store_unforce_plan pour agir sur les plans de répartiteur ou de variantes.

Si une variante est forcée, le répartiteur parent n’est pas forcé. Si un répartiteur est forcé, seules les variantes de ce répartiteur sont considérées comme utilisables :

• Les variantes auparavant forcées par d’autres répartiteurs deviennent inactives, mais elles conservent leur statut forcé jusqu'à ce que leur répartiteur soit à nouveau forcé.
• Les variantes précédemment forcées dans le même répartiteur qui est devenu inactif sont de nouveau forcées

Comportement du hint de requête du Query Store

• Lorsqu’un indicateur du Magasin des requêtes est ajouté à une variante de requête (requête enfant), l’indicateur est appliqué de la même manière qu’une requête non-PSP. Les indicateurs de variante de requête ont une priorité plus élevée si un indicateur a également été appliqué à la requête parente dans le Magasin des requêtes.

• Lorsqu’un indicateur du Magasin des requêtes est ajouté à la requête parente et que la requête enfant (variant de requête) n’a pas d’indicateur du Magasin des requêtes existant, la requête enfant (variante de requête) hérite de l’indicateur de la requête parente.

• Si un indicateur de requête du Magasin des requêtes est supprimé de la requête parente, les requêtes enfants (variantes de requête) ont également l’indicateur supprimé.

• Si un indicateur RECOMPILE est ajouté à la requête parente, le système générera des plans non PSP une fois que les plans des variantes de requêtes existants auront été retirés du cache des plans, car la fonction PSP ne s'applique pas aux requêtes qui incluent un indicateur RECOMPILE.

• Les résultats des hints du Magasin de requêtes peuvent être observés à l'aide des événements étendus query_store_hints_application_success et query_store_hints_application_failed. Pour la table sys.query_store_query_hints, elle contient des informations concernant l’indicateur de requête qui a été appliqué. Si l'indice n'a été appliqué qu'à une requête parente, le catalogue système contient les informations de l'indice pour la requête parente, mais pas pour ses requêtes enfants, même si les requêtes enfants héritent de l'indice de la requête parente.

Le PSP avec des indices de requête et un comportement de forçage du plan peut être résumé dans le tableau suivant :

Indication de variante de requête ou plan	Le parent a un indice appliqué par l’utilisateur	Le parent a un indice avec commentaires appliqués	Le parent a imposé un plan manuellement	Le parent dispose d’un plan forcé d’APC 1
Indicateur via l’utilisateur	Indication de variante de requête	Suggestion de variante de requête	Indication de variante de requête	N/A
Conseil par le biais de commentaires	Indice de variante de requête	Indice de variante de requête	Suggestion pour la variante de requête	N/A
Plan forcé par l’utilisateur	Variant de requête plan imposé	Variant de requête plan imposé	Variant de requête plan imposé	Variant de requête plan imposé
Plan imposé par APC	Variant de requête plan imposé	Variant de requête plan imposé	Variant de requête plan imposé	Variant de requête plan imposé
Aucun indicateur ou plan forcé	Indicateur de l’utilisateur parent	Aucun indicateur	Aucune action	Aucune action

¹ Composant de correction de plan automatique de la fonctionnalité de réglage automatique

Événements étendus

• parameter_sensitive_plan_optimization_skipped_reason : se produit quand la caractéristique de plan sensible aux paramètres est ignorée. Utilisez cet événement afin de surveiller la raison pour laquelle l’optimisation du PSP est ignoré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] = 'psp_skipped_reason_enum'
  ORDER BY map_key;
  

• parameter_sensitive_plan_optimization : se produit quand une requête utilise la fonctionnalité d’optimisation PSP. Canal de débogage uniquement. Certains champs d’intérêt peuvent être les suivants :

  • is_query_variant : décrit s’il s’agit d’un plan de répartiteur (parent) ou d’un plan de variante de requête (enfant)
  • predicate_count : nombre de prédicats sélectionnés par le PSP
  • query_variant_id : affiche l’ID de variante de requête. La valeur 0 signifie que l’objet est un plan de répartiteur (parent).

• query_with_parameter_sensitivity: Cet événement, lorsqu'il est déclenché, affiche le nombre de prédicats que la fonctionnalité a trouvés intéressants, plus de détails au format JSON concernant les prédicats intéressants, ainsi que si le PSPO est pris en charge pour le ou les prédicats.

• Exemple de sortie de l’événement query_with_parameter_sensitivity étendu

Field	Value
interesting_predicate_count	3
interesting_predicate_details	{"Predicates":[{"ColumnId":7,"TableId":1221579390,"Skewness":475038.75},{"ColumnId":7,"TableId":1221579390,"Skewness":475038.75},{"ColumnId":7,"TableId":1221579390,"Skewness":475038.75}]}
psp_optimization_supported	True
query_type	195

Comportement d’audit de SQL Server

L’optimisation PSP fournit des données d’audit pour la déclaration de plan de répartition, ainsi que toutes les variantes de requête associées au dispatcheur. La colonne additional_information dans SQL Server Audit fournit également les informations de pile T-SQL appropriées pour les variantes de requête. À l’aide de la base de données MyNewDatabase comme exemple, si cette base de données a une table appelée T2 et une procédure stockée portant le nom usp_test, après l’exécution de la procédure stockée usp_test, le journal d’audit peut contenir les entrées suivantes :

action_id	object_name	statement	additional_information
AUSC			<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[Audit_Testing_Psp$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info>
EX	usp_test	exécuter usp_test 300
SL	T2	sélectionnez * de dbo.t2 où ID=@id	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL	T2	select * from dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 1, predicate_range([MyNewDatabase].[dbo].[T2].[ID] = @id, 100.0, 100000.0)))	tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
EX	usp_test	exec usp_test 60000
SL	T2	sélectionnez * de dbo.t2 où ID=@id	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL	T2	select * from dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 3, predicate_range([TestDB_FOR_PSP].[ dbo]. [T2]. [ID] = @id, 100.0, 100000.0)))	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>

Problèmes connus

Issue	Date de la détection	Status	Date de la résolution
Une exception de violation d’accès se produit dans le magasin des requêtes SQL Server 2022 (16.x) sous certaines conditions. Vous pouvez rencontrer des exceptions de violation d’accès lorsque l’intégration du Magasin de requêtes d’optimisation PSP est activée. Pour plus d’informations, consultez la mise à jour dans l’optimisation du plan sensible aux paramètres, Pourquoi ?	Mars 2023	Resolved	Août 2023 (SQL Server 2022 CU 7)
Une exception de violation d'accès peut se produire sur des réplicas secondaires lisibles sous certaines conditions dans SQL Server 2025 (17.x). Vous pouvez rencontrer des exceptions de violation d’accès lorsque l’optimisation PSP est activée sur une base de données secondaire lisible configurée pour utiliser le Magasin des requêtes pour la fonctionnalité de réplicas secondaires lisibles.	Septembre 2025	Resolved	Janvier 2026 (SQL Server 2025 CU 1)

Resolved

Une exception de violation d’accès peut se produire sur des réplicas secondaires lisibles dans certaines conditions

(Résolu en janvier 2026)

Les requêtes qui satisfont aux conditions suivantes peuvent rencontrer une violation d’accès lorsqu’une variante de requête de plan sensible aux paramètres (PSP) n’est pas en mesure de déterminer l’état persistant de son instruction de répartiteur parent :

• Exécuté sur une réplique secondaire
• Sensible à la détection de paramètre
• Éligible pour l'optimisation du plan sensible aux paramètres (PSP)

Une exception de violation d’accès se produit dans le magasin des requêtes SQL Server 2022 sous certaines conditions

(Résolu en mars 2023)

Note

SQL Server 2022 (16.x) Mise à jour cumulative 7 a publié plusieurs correctifs pour une condition de concurrence pouvant entraîner une violation d’accès.

Ce problème s'est produit en raison d'une condition de compétition qui peut être provoquée lorsque les statistiques d'exécution d'une requête exécutée sont conservées depuis la représentation en mémoire du Magasin des requêtes (trouvée dans le MEMORYCLERK_QUERYDISKSTORE_HASHMAP gestionnaire de mémoire) vers la version sur disque du Magasin des requêtes. Les statistiques d’exécution, indiquées en tant que Statistiques du runtime, sont conservées en mémoire pendant une période définie avec l’option DATA_FLUSH_INTERVAL_SECONDS de l’instruction SET QUERY_STORE (la valeur par défaut est 15 minutes). Vous pouvez utiliser la boîte de dialogue Query Store de Management Studio pour entrer une valeur pour l'Intervalle de vidage des données (Minutes), qui est convertie en secondes en interne. Si le système est soumis à une sollicitation élevée de la mémoire, les statistiques d’exécution peuvent être vidées sur le disque plus tôt que ce qui est défini avec l’option DATA_FLUSH_INTERVAL_SECONDS. Lorsque d’autres threads en arrière-plan liés au nettoyage des plans de requêtes du Magasin des requêtes (c’est-à-dire les options STALE_QUERY_THRESHOLD_DAYS et/ou MAX_STORAGE_SIZE_MB du Magasin des requêtes) sont en cours d'exécution, il existe un scénario dans lequel une variante de requête et/ou son instruction de répartiteur associée peut être déréférencée prématurément. Cela peut entraîner une violation d’accès pendant les opérations d’insertion ou de suppression de variantes de requête dans le Magasin des requêtes.

Pour plus d’informations sur les opérations du Magasin des requêtes, reportez-vous à la section Remarques de l’article Comment le Magasin des requêtes collecte des données.

Contenu connexe

• Recompilation des plans d’exécution
• Réutilisation des paramètres et des plans d’exécution
• Paramétrage simple
• Paramétrage forcé
• Indicateurs de requête (Transact-SQL)
• Traitement de requêtes intelligent dans les bases de données SQL
• Sensibilité des paramètres
• MODIFIER LA CONFIGURATION DE L’ÉTENDUE DE LA BASE DE DONNÉES (Transact-SQL)