Optimisation du plan de sensibilité aux paramètres

Article
01/08/2024

S’applique à : SQL Server 2022 (16.x) et versions ultérieures, Azure SQL Database, Azure SQL Managed Instance

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 :

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 de la ou des valeurs de paramètre d’exécution fournies par le client.

Comprendre la paramétrisation

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 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, 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 du plan de sensibilité des paramètres, 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 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 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 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.

Variante de requête

Comme un plan de répartiteur évalue la cardinalité 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ê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 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é 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 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 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.
L’affichage catalogue du système de Magasin des requêtes sys.query_store_plan (Transact-SQL) 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 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 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 du 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 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 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 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 n’est rien qui peut être mappé à un objet directement dans 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 documentation sys.dm_exec_plan_attributes 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 d’un 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 n’est pas 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 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 qu’instruction préparée, le object_id n’est pas automatiquement exposée dans les différents DMV associés sys.dm_exec_* au cache de plan sans fragmenter 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é. Le object_id est exposé dans le Magasin des requêtes, car le 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 des insights supplémentaires sur la fonctionnalité d’optimisation PSP, nous recommandons d’activer l’intégration du Magasin des requêtes en activant le Magasin des requêtes. L’exemple suivant va activer 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
);

Remarque

À 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 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, la configuration délimitée à la base de données PARAMETER_SNIFFING ou l’indicateur de requête USE HINT('DISABLE_PARAMETER_SNIFFING'), l’optimisation du 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 CACHESTORE_OBJCP et CACHESTORE_SQLCP 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 du 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 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 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 du Magasin des requêtes

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è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 indicateur RECOMPILE.
Les résultats des indicateurs du Magasin des requêtes peuvent être observés à l’aide des événements étendus query_store_hints_application_success et des événements 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’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.

Le 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	Interroger une variante de requête	Interroger une variante de requête	Interroger une variante de requête	S/O
Conseil par le biais de commentaires	Interroger une variante de requête	Interroger une variante de requête	Interroger une variante de requête	S/O
Plan forcé par l’utilisateur	Variante de requête plan forcé	Variante de requête plan forcé	Variante de requête plan forcé	Variante de requête plan forcé
Plan forcé par APC	Variante de requête plan forcé	Variante de requête plan forcé	Variante de requête plan forcé	Variante 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 afin de superviser le motif pour lequel 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).

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 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	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
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 de l’optimisation du PSP Magasin des requêtes est activée. Pour plus d’informations, consultez la mise à jour dans Optimisation du plan de sensibilité des paramètres. Pourquoi ?.	Mars 2023	Résolu	Août 2023 (CU 7)

Résolu

Une exception de violation d’accès se produit dans le magasin des requêtes SQL Server 2022 sous 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 du PSP avec l’intégration des Magasin des requêtes 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 régisseur de mémoire MEMORYCLERK_QUERYDISKSTORE_HASHMAP) 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 Magasin des requêtes de Management Studio pour entrer une valeur pour 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 d’arrière-plan Magasin des requêtes liés au nettoyage du plan de requête du Magasin des requêtes (c’est-à-dire les options STALE_QUERY_THRESHOLD_DAYS, et/ou MAX_STORAGE_SIZE_MB Magasin des requêtes), pour les requêtes à partir du Magasin des requêtes, il existe un scénario dans lequel une variante de requête et/ou son instruction de dispatcher associée peuvent devenir 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.

Solution de contournement : les variantes de requête figurant dans le 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 le 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 du 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 volumineux 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 le magasin des requêtes, la désactivation du magasin des requêtes peut prendre un certain temps. Pour désactiver de force le magasin des requêtes dans ces scénarios, utilisez la commande ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED) à la 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.

Partage via

Optimisation du plan de sensibilité aux paramètres

Comprendre la paramétrisation

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

Terminologie

Expression dispatcher

Plan de répartiteur

Variante de requête

Plage de cardinalité de prédicat

Notes

À propos de l’installation

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

Comportement d’indicateur de requête du Magasin des requêtes

Événements étendus

Comportement d’audit SQL Server

Problèmes connus

Résolu

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

Commentaires

Ressources supplémentaires

Partage via

Optimisation du plan de sensibilité aux paramètres

Comprendre la paramétrisation

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

Terminologie

Expression dispatcher

Plan de répartiteur

Variante de requête

Plage de cardinalité de prédicat

Notes

À propos de l’installation

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

Comportement d’indicateur de requête du Magasin des requêtes

Événements étendus

Comportement d’audit SQL Server

Problèmes connus

Résolu

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

Contenu connexe

Commentaires

Ressources supplémentaires