Optimisation du plan de sensibilité aux paramètres

Article
01/08/2024

S’applique à : SQL Server 2022 (16.x) et versions ultérieures

L’optimisation du plan sensible aux paramètres fait partie de la famille intelligente de fonctionnalités de traitement des requêtes. Il traite du scénario dans lequel un plan mis en cache unique pour une requête paramétrable n’est pas optimal pour toutes les valeurs de paramètres 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 :

L’optimisation du plan de sensibilité des paramètres active automatiquement plusieurs plans mis en cache actifs pour une instruction paramétrable unique. Les plans d’exécution mis en cache prennent en charge différentes tailles de données en fonction 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 Transact-SQL (T-SQL) augmente la capacité du moteur relationnel à faire correspondre de nouvelles instructions T-SQL avec des plans d’exécution compilés précédemment existants et à promouvoir la réutilisation du plan. Pour plus d’informations, consultez Paramétrisation simple.

Vous pouvez également remplacer le comportement de paramétrage simple par défaut de SQL Server en spécifiant que tous les SELECTINSERTinstructions, et UPDATEDELETE instructions d’une base de données sont paramétrables, soumis à certaines limitations. Pour plus d'informations, consultez Paramétrage forcé.

Implémentation de l’optimisation du plan de sensibilité des paramètres

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 , jusqu’à trois des 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 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é.

Terminologie

Expression dispatcher

Évalue carte inalité des prédicats en fonction des valeurs des paramètres d’exécution et route l’exécution vers différentes variantes de requête.

Plan de répartiteur

Un plan contenant l’expression de répartiteur est mis en cache pour la requête d’origine. 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 de limites élevées et basses . Ces valeurs sont utilisées pour diviser les valeurs de paramètre 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

En tant que plan de répartiteur évalue la carte inalité des prédicats en fonction des valeurs des paramètres d’exécution, il compartimente ces valeurs et génère des requêtes enfants distinctes à compiler et à exécuter. Ces requêtes enfants sont appelées « variantes de requête ». Les variantes de requête ont leurs propres plans dans le cache du plan et dans le Magasin des requêtes.

Plage de prédicats carte inalité

Au moment de l’exécution, la carte inalité de chaque prédicat est évaluée en fonction des valeurs des paramètres d’exécution. Le répartiteur compartimente les valeurs de carte inalité en trois plages de prédicat carte inalité 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 en variantes de requête en fonction des valeurs d’exécution des paramètres. 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 de paramètres donné pour calculer la plage d’carte inalité.
le répartiteur mappe ces plages à des variantes de requête spécifiques et 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 carte inality 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 indicateur d’optimisation PSP généré est ajouté à l’instruction SQL dans le code XML ShowPlan d’une variante de requête. L’indicateur ne peut pas être utilisé directement et n’est pas analysé manuellement. L’indicateur contient 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 carte inalité prédicées générées à partir de l’expression du répartiteur.

Et, dans le code 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>

Notes

La fonctionnalité d’optimisation 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 variante de requête se recompilent indépendamment selon les besoins, comme avec tout autre type de plan de requête, sous réserve d’événements de recompilation par défaut. Pour plus d’informations sur la recompilation, consultez Recompilation des plans d’exécution.
L’sys.query_store_plan (Transact-SQL) Magasin des requêtes vue catalogue système 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 de catalogue système Magasin des requêtes, sys.query_store_query_variant (Transact-SQL), contient des informations sur les relations parent-enfant entre les requêtes paramétrables 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 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, table1seul 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, 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, 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 PLAN PER VALUE PSPassocié.

<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 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 délimitées à la base de données qui tentent de réduire le modèle CE en cours d’utilisation ou d’influencer les hypothèses que le modèle CE effectue 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 candidature de ces prédicats.
- Pour plus d’informations, consultez la section Augmentation de l’hypothèse de corrélation pour plusieurs prédicats de l’optimisation de vos plans de requête avec le livre blanc de 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 du modèle CE hérité peut affecter la sélectivité des prédicats dans un scénario de jointure à plusieurs colonnes peut être 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 modules parents si object_id 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, ce object_id n’est rien qui peut être mappé à un objet directement sys.objects , mais il s’agit essentiellement d’une valeur calculée basée sur un hachage interne du texte de lot. Pour plus d’informations, consultez la section Table retournée de la sys.dm_exec_plan_attributes documentation DMV.

Les plans de variante de requête sont placés dans le magasin d’objets du cache de plan (CACHESTORE_OBJCP) tandis que les plans de répartiteur sont placés dans le magasin de cache plans SQL (CACHESTORE_SQLCP). Toutefois, la fonctionnalité PSP stocke le object_id parent d’une variante de requête dans l’attribut ObjectID qui fait partie de l’indicateur PLAN PER VALUE que PSP ajoute au code XML ShowPlan si la requête parente fait partie d’un module et non dynamique ou ad hoc T-SQL. 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 sys.dm_exec_query_stats vue DMV, contiennent toujours des données pour les variantes de requête, mais l’association entre les object_id variantes de requête et les objets de la sys.objects table 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 de l’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 qu’instruction préparée, elles object_id ne sont pas automatiquement exposées dans les différentes vues DMV associées sys.dm_exec_* au cache de plan sans déchiqueter le code XML ShowPlan et appliquer des techniques de correspondance de modèle de texte (autrement dit, traitement XQuery supplémentaire). Seuls les plans de répartiteur d’optimisation PSP émettent actuellement l’ID d’objet parent approprié. L’objet object_id est exposé dans le Magasin des requêtes, car Magasin des requêtes permet un modèle relationnel plus important que la hiérarchie du cache de plan. Pour plus d’informations, consultez la vue de catalogue système Magasin des requêtes sys.query_store_query_variant (Transact-SQL).

À propos de l’installation

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 vous recommandons d’activer Magasin des requêtes intégration en activant le Magasin des requêtes. L’exemple suivant active l’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
);

Remarque

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

Pour désactiver l’optimisation du plan de sensibilité des paramètres au niveau de la base de données, utilisez la configuration délimitée à la base de données ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF.
Pour désactiver l’optimisation du plan de sensibilité des paramètres au niveau de la requête, utilisez l’indicateur de requête DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION.
Si la détection de paramètre est désactivée par l’indicateur de trace 4136, PARAMETER_SNIFFING la configuration délimitée à la base de données ou l’indicateur USE HINT('DISABLE_PARAMETER_SNIFFING') de requête, 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 (Transact-SQL) - Requête et ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).
Le nombre de variantes de plan uniques par répartiteur stocké dans le cache du plan est limité pour éviter les ballonnements 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 que les tailles des magasins de cache et CACHESTORE_SQLCPde CACHESTORE_OBJCP cache sont excessives, vous devez envisager d’appliquer l’indicateur de trace 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 variante de requête pour tous les répartiteurs appartenant à une requête parente. Envisagez d’augmenter l’option max_plans_per_query de configuration Magasin des requêtes.
- Un exemple de la façon dont le nombre de plans uniques peut dépasser la limite de Magasin des requêtes max_plans_per_query par défaut serait un scénario dans lequel vous avez 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 standard (non répartiteur). 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 également une moyenne de cinq plans, il est possible dans ce scénario d’avoir plus de 200 plans dans la Magasin des requêtes pour une requête parente. Cela dépend également de l’asymétrie importante des données dans le ou les jeux de données que cet exemple de requête parente peut 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 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 du 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 Magasin des requêtes. Cette colonne est disponible dans sys.query_store_query et d’autres tables de catalogue du Magasin des requêtes.

Forçage de plan dans le Magasin 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 précédemment forcées d’autres répartiteurs deviennent inactives, mais conservent l’état forcé jusqu’à ce que leur répartiteur soit forcé à nouveau
Les variantes précédemment forcées dans le même répartiteur qui étaient devenues inactives sont de nouveau forcées

comportement d’indicateur de requête Magasin des requêtes

Lorsqu’un indicateur de 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 Magasin des requêtes.
Lorsqu’un indicateur de 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 de 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 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 RECOMPILE indicateur est ajouté à la requête parente, le système génère des plans non-PSP une fois que les plans de variante de requête existants ont été supprimés du cache du plan, car la fonctionnalité PSP ne fonctionne pas sur les requêtes qui ont un RECOMPILE indicateur.
Magasin des requêtes résultats des indicateurs peuvent être observés à l’aide des événements et query_store_hints_application_failed événements query_store_hints_application_success étendus. Pour la table sys.query_store_query_hints , elle contient des informations concernant l’indicateur de requête qui a été appliqué. Si l’indicateur n’a été appliqué qu’à une requête parente, le catalogue système contient les informations d’indicateur de la requête parente, mais pas pour ses requêtes enfants, bien que les requêtes enfants héritent de l’indicateur de la requête parente.

PSP avec des indicateurs de requête et un comportement de forçage de plan peuvent être résumés dans le tableau suivant :

Indicateur ou plan de variante de requête	Le parent a un indicateur appliqué par l’utilisateur	Le parent a un indicateur appliqué aux commentaires	Le parent a un plan forcé manuellement	Le parent dispose d’un plan forcé d’APC ¹
Indicateur via l’utilisateur	Indicateur de variante de requête	Indicateur de variante de requête	Indicateur de variante de requête	S/O
Conseil par le biais de commentaires	Indicateur de variante de requête	Indicateur de variante de requête	Indicateur de variante de requête	S/O
Planifier forcé par l’utilisateur	Variant de requête plan forcé	Variant de requête plan forcé	Variant de requête plan forcé	Variant de requête plan forcé
Plan forcé par APC	Variant de requête plan forcé	Variant de requête plan forcé	Variant de requête plan forcé	Variant de requête plan forcé
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 fonctionnalité de plan sensible aux paramètres est ignorée. Utilisez cet événement pour surveiller la raison pour laquelle l’optimisation PSP est ignorée.

La requête suivante montre toutes les raisons possibles pour lesquelles PSP a été ignorée :
```
SELECT name, map_value FROM sys.dm_xe_map_values WHERE name ='psp_skipped_reason_enum' ORDER BY map_key;
```
parameter_sensitive_plan_optimization: se produit lorsqu’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 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).

Comportement d’audit SQL Server

L’optimisation PSP fournit des données d’audit pour l’instruction de plan de répartiteur et toutes les variantes de requête associées au répartiteur. La additional_information colonne dans SQL Server Audit fournit également les informations de pile T-SQL appropriées pour les variantes de requête. À l’aide de la MyNewDatabase base de données 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	instruction	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	exec usp_test 300
SL	T2	select * from dbo.t2 where 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	select * from dbo.t2 where 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

Problème	Date de la détection	Statut	Date de la résolution
L’exception violation d’accès se produit dans Magasin des requêtes dans SQL Server 2022 (16.x) dans certaines conditions. Vous pouvez rencontrer des exceptions de violation d’accès lorsque l’optimisation de PSP Magasin des requêtes l’intégration est activée. Pour plus d’informations, consultez la mise à jour dans l’optimisation du plan sensible aux paramètres, Pourquoi ?.	Mars 2023	Résolu	Août 2023 (CU 7)

Résolu

L’exception de violation d’accès se produit dans Magasin des requêtes dans SQL Server 2022 dans certaines conditions

Remarque

À compter de SQL Server 2022 (16.x) Mise à jour cumulative 7, plusieurs correctifs pour une condition de concurrence pouvant entraîner une violation d’accès ont été libérés. Si des violations d’accès liées à l’optimisation PSP avec Magasin des requêtes intégration se produisent après l’application de la mise à jour cumulative 7 pour SQL Server 2022 (16.x), tenez compte de la section de solution de contournement suivante.

Ce problème se produit en raison d’une condition de concurrence qui peut être provoquée lorsque les statistiques d’exécution d’une requête exécutée sont conservées à partir de la représentation en mémoire du Magasin des requêtes (trouvé dans le MEMORYCLERK_QUERYDISKSTORE_HASHMAP commis de mémoire) vers la version sur disque du Magasin des requêtes. Les statistiques d’exécution, affichées en tant que statistiques d’exécution, sont conservées en mémoire pendant une période donnée, définies par l’option DATA_FLUSH_INTERVAL_SECONDS de l’instruction SET QUERY_STORE (la valeur par défaut est de 15 minutes). Vous pouvez utiliser la boîte de dialogue Magasin des requêtes Management Studio pour entrer une valeur pour l’intervalle de vidage des données (minutes), qui est converti en interne en secondes. Si le système est sous pression mémoire, les statistiques d’exécution peuvent être vidées sur le disque plus tôt que définie avec l’option DATA_FLUSH_INTERVAL_SECONDS . Lorsque d’autres threads d’arrière-plan Magasin des requêtes liés à Magasin des requêtes plan de requête propre up (c’est-à-direSTALE_QUERY_THRESHOLD_DAYS, et/ou MAX_STORAGE_SIZE_MB Magasin des requêtes options), les requêtes de l’Magasin des requêtes, il existe un scénario dans lequel une variante de requête et/ou son instruction de répartiteur associée peuvent devenir déréférentes 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 de Magasin des requêtes, reportez-vous à la section Remarques de l’article Comment Magasin des requêtes collecte des données.

Solution de contournement : les variantes de requête figurant dans la Magasin des requêtes peuvent être supprimées, ou la fonctionnalité PSP peut être temporairement désactivée au niveau de la requête ou de la base de données jusqu’à ce que des correctifs supplémentaires soient disponibles si votre système rencontre toujours des violations d’accès dans Magasin des requêtes avec l’intégration PSP activée après l’application de la mise à jour cumulative 7 pour SQL Server 2022 (16.x).

Pour désactiver l’optimisation du plan de sensibilité des paramètres au niveau de la base de données, utilisez la configuration délimitée à la base de données ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF.
Pour désactiver l’optimisation du plan de sensibilité des paramètres au niveau de la requête, utilisez l’indicateur de requête DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION.

Pour supprimer toutes les variantes de requête de l’Magasin des requêtes, pas seulement les variantes de requête qui apparaissent dans l’affichage catalogue sys.query_store_query_variant (Transact-SQL), une requête similaire à celle suivante peut être utilisée. Remplacez [<database>] par la base de données appropriée qui rencontrait des problèmes :

USE master;
GO

--Temporarily turn Query Store off in order to remove query variant plans as well as to
--clear the Query Store in-memory representation of Query Store (HashMap) for a particular database
ALTER DATABASE [<database>] SET QUERY_STORE = OFF;
GO

USE [<database>];
GO

DECLARE @QueryIDsCursor CURSOR;
DECLARE @QueryID BIGINT;
BEGIN
 -- Getting the cursor for query IDs for query variant plans
    SET @QueryIDsCursor = CURSOR FAST_FORWARD FOR
    SELECT query_id
        FROM sys.query_store_plan
    WHERE plan_type = 2 --query variant plans
    ORDER BY query_id;
 
 -- Using a non-set based method for this example query
    OPEN @QueryIDsCursor
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
        WHILE @@FETCH_STATUS = 0
    BEGIN

 -- Deleting query variant(s) from the query store
        EXEC sp_query_store_remove_query @query_id = @QueryID;
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
    END;
    CLOSE @QueryIDsCursor ;
    DEALLOCATE @QueryIDsCursor;
END;

--Turn Query Store back on
ALTER DATABASE [<database>] SET QUERY_STORE = ON;
GO

Si votre Magasin des requêtes est volumineuse ou si votre système a une charge de travail importante et/ou un nombre élevé de requêtes non paramétrables ad hoc qui peuvent être capturées par Magasin des requêtes, la désactivation de l’Magasin des requêtes peut prendre un certain temps. Pour désactiver de force la Magasin des requêtes dans ces scénarios, utilisez la commande à la ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED) place, dans l’exemple T-SQL précédent. Pour rechercher des requêtes non paramétrables, consultez Rechercher des requêtes non paramétrables dans Magasin des requêtes.

Optimisation du plan de sensibilité aux paramètres

Comprendre le paramétrage

Implémentation de l’optimisation du plan de sensibilité des paramètres

Terminologie

Expression dispatcher

Plan de répartiteur

Variant de requête

Plage de prédicats carte inalité

Notes

À propos de l’installation

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

comportement d’indicateur de requête Magasin des requêtes

Événements étendus

Comportement d’audit SQL Server

Problèmes connus

Résolu

L’exception de violation d’accès se produit dans Magasin des requêtes dans SQL Server 2022 dans certaines conditions

Commentaires

Commentaires

Ressources supplémentaires

Optimisation du plan de sensibilité aux paramètres

Comprendre le paramétrage

Implémentation de l’optimisation du plan de sensibilité des paramètres

Terminologie

Expression dispatcher

Plan de répartiteur

Variant de requête

Plage de prédicats carte inalité

Notes

À propos de l’installation

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

comportement d’indicateur de requête Magasin des requêtes

Événements étendus

Comportement d’audit SQL Server

Problèmes connus

Résolu

L’exception de violation d’accès se produit dans Magasin des requêtes dans SQL Server 2022 dans certaines conditions

Contenu connexe

Commentaires

Commentaires

Ressources supplémentaires