Surveillance des performances à l’aide du magasin de requêtesMonitoring performance by using the Query Store

S’applique à :Applies to: ouiSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) et ultérieures OuiAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database OuiAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance ouiAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse AnalyticsS’applique à :Applies to: ouiSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later OuiAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database OuiAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance ouiAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

La fonctionnalité de magasin de requêtes SQL ServerSQL Server vous fournit des informations sur le choix de plan de requête et sur les performances.The SQL ServerSQL Server Query Store feature provides you with insight on query plan choice and performance. Elle simplifie la résolution des problèmes de performances en vous permettant de trouver rapidement les différences de performances provoquées par des changements de plan de requête.It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Le magasin de requête capture automatiquement l'historique des requêtes, des plans et des statistiques d'exécution et les conserve à des fins de révision.Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. Elle sépare les données en périodes, ce qui vous permet de voir les modèles d'utilisation de base de données et de comprendre à quel moment les changements de plan de requête ont eu lieu sur le serveur.It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server. Vous pouvez configurer le magasin de requêtes à l’aide de l’option ALTER DATABASE SET .You can configure query store using the ALTER DATABASE SET option.

Pour plus d’informations sur l’utilisation du Magasin des requêtes dans Azure SQL DatabaseSQL Database, consultez Utilisation du Magasin des requêtes dans Azure SQL Database.For information about operating the Query Store in Azure SQL DatabaseSQL Database, see Operating the Query Store in Azure SQL Database.

Important

Si vous utilisez le Magasin des requêtes pour avoir un aperçu juste-à-temps de la charge de travail dans SQL Server 2016 (13.x)SQL Server 2016 (13.x), prévoyez d’installer les correctifs d’évolutivité des performances dans KB 4340759 dès que possible.If you are using Query Store for just in time workload insights in SQL Server 2016 (13.x)SQL Server 2016 (13.x), plan to install the performance scalability fixes in KB 4340759 as soon as possible.

Activation du magasin de requêtesEnabling the Query Store

Le Magasin des requêtes est activé par défaut pour les nouvelles bases de données Azure SQL Database, mais non pour les nouvelles bases de données SQL Server et Azure Synapse Analytics.Query Store is not enabled by default for new SQL Server and Azure Synapse Analytics databases, and is enabled by default for new Azure SQL Database databases.

Utiliser la page Magasin des requêtes dans SQL Server Management StudioSQL Server Management StudioUse the Query Store Page in SQL Server Management StudioSQL Server Management Studio

  1. Dans l’Explorateur d’objets, cliquez avec le bouton droit sur une base de données, puis sur Propriétés.In Object Explorer, right-click a database, and then click Properties.

    Notes

    Nécessite au moins la version 16 de Management StudioManagement Studio.Requires at least version 16 of Management StudioManagement Studio.

  2. Dans la boîte de dialogue Propriétés de la base de données , sélectionnez la page Magasin de requêtes .In the Database Properties dialog box, select the Query Store page.

  3. Dans la zone Mode d’opération (demandé) , sélectionnez Lecture Écriture.In the Operation Mode (Requested) box, select Read Write.

Utilisation d’instructions Transact-SQLUse Transact-SQL Statements

Utilisez l’instruction ALTER DATABASE pour activer le Magasin des requêtes pour une base de données déterminée.Use the ALTER DATABASE statement to enable the query store for a given database. Par exemple :For example:

SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Pour obtenir d’autres options de syntaxe relatives au magasin des requêtes, consultez Options ALTER DATABASE SET (Transact-SQL).For more syntax options related to the Query Store, see ALTER DATABASE SET Options (Transact-SQL).

Notes

Vous ne pouvez pas activer le magasin des requêtes pour les bases de données master ou tempdb.Query Store cannot be enabled for the master or tempdb databases.

Important

Pour plus d’informations sur l’activation du Magasin des requêtes et la manière de le garder ajusté à votre charge de travail, reportez-vous à Bonnes pratiques concernant le magasin de requêtes.For information on enabling Query Store and keeping it adjusted to your workload, refer to Best Practice with the Query Store.

Informations sur le Magasin des requêtesInformation in the Query Store

Les plans d’exécution d’une requête spécifique dans SQL ServerSQL Server évoluent généralement au fil du temps pour un certain nombre de raisons, telles que les modifications des statistiques, les modifications de schémas, la création/suppression d’index, etc. Le cache de procédures (où sont stockés les plans de requête mis en cache) stocke uniquement le dernier plan d'exécution.Execution plans for any specific query in SQL ServerSQL Server typically evolve over time due to a number of different reasons such as statistics changes, schema changes, creation/deletion of indexes, etc. The procedure cache (where cached query plans are stored) only stores the latest execution plan. Les plans sont également supprimés du cache du plan en raison de la sollicitation de la mémoire.Plans also get evicted from the plan cache due to memory pressure. Par conséquent, les régressions des performances de requête provoquées par des modifications du plan d'exécution peuvent être significatives et longues à résoudre.As a result, query performance regressions caused by execution plan changes can be non-trivial and time consuming to resolve.

Comme le magasin des requêtes conserve plusieurs plans d’exécution par requête, il peut appliquer des stratégies pour indiquer au processeur de requêtes d’utiliser un plan d’exécution spécifique pour une requête.Since the Query Store retains multiple execution plans per query, it can enforce policies to direct the Query Processor to use a specific execution plan for a query. On parle alors de forçage de plan.This is referred to as plan forcing. Le forçage de plan dans un magasin de requêtes est fourni à l'aide d'un mécanisme semblable à l’indicateur de requête USE PLAN , mais il ne nécessite pas d’apporter des modifications dans les applications utilisateur.Plan forcing in Query Store is provided by using a mechanism similar to the USE PLAN query hint, but it does not require any change in user applications. Le forçage de plan peut résoudre une régression des performances de requête provoquée par une modification du plan dans un délai très court.Plan forcing can resolve a query performance regression caused by a plan change in a very short period of time.

Notes

Le magasin des requêtes collecte des plans pour les instructions DML telles que SELECT, INSERT, UPDATE, DELETE, MERGE et BULK INSERT.Query Store collects plans for DML Statements such as SELECT, INSERT, UPDATE, DELETE, MERGE, and BULK INSERT.

Par défaut, le magasin des requêtes ne collecte pas de données pour les procédures stockées compilées en mode natif.Query Store does not collect data for natively compiled stored procedures by default. Utilisez sys. sp_xtp_control_query_exec_stats pour activer la collecte des données pour les procédures stockées compilées en mode natif.Use sys.sp_xtp_control_query_exec_stats to enable data collection for natively compiled stored procedures.

Les statistiques d’attente sont une autre source d’informations qui aide à résoudre les problèmes de performances dans Moteur de base de donnéesDatabase Engine.Wait stats are another source of information that helps to troubleshoot performance in the Moteur de base de donnéesDatabase Engine. Pendant longtemps, les statistiques d’attente ont été disponibles seulement au niveau de l’instance, ce qui rendait difficile leur rétroaction sur une requête spécifique.For a long time, wait statistics were available only on instance level, which made it hard to backtrack waits to a specific query. À compter de SQL Server 2017 (14.x)SQL Server 2017 (14.x) et de Azure SQL DatabaseAzure SQL Database, le magasin des requêtes comprend une dimension qui effectue le suivi des statistiques d’attente. L’exemple suivant active la collecte des statistiques d’attente par le magasin des requêtes.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Azure SQL DatabaseAzure SQL Database, Query Store includes a dimension that tracks wait stats. The following example enables the Query Store to collect wait stats.

SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

Voici des scénarios courants pour l'utilisation de la fonctionnalité de magasin de requêtes :Common scenarios for using the Query Store feature are:

  • Recherchez et corrigez rapidement une régression des performances du plan en forçant l’application du plan de requête précédent.Quickly find and fix a plan performance regression by forcing the previous query plan. Résolvez les requêtes qui ont récemment rencontré une régression des performances suite à la modification du plan d'exécution.Fix queries that have recently regressed in performance due to execution plan changes.
  • Déterminez le nombre de fois où une requête a été exécutée dans une fenêtre de temps donnée, en aidant un administrateur de base de données à résoudre les problèmes liés aux ressources de performances.Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
  • Identifiez les n requêtes les plus importantes (en termes de temps d’exécution, de consommation de mémoire, etc.) au cours des x dernières heures.Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.
  • Auditez l'historique des plans de requête pour une requête donnée.Audit the history of query plans for a given query.
  • Analysez les ressources (UC, E/S et mémoire) des modèles d'utilisation pour une base de données spécifique.Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.
  • Identifiez les n premières requêtes qui attendent des ressources.Identify top n queries that are waiting on resources.
  • Comprenez la nature de l’attente d’une requête ou d’un plan en particulier.Understand wait nature for a particular query or plan.

Le magasin des requêtes contient trois magasins :The Query Store contains three stores:

  • Un magasin de plans pour rendre persistantes les informations du plan d’exécution.a plan store for persisting the execution plan information.
  • Un magasin de statistiques de runtime pour rendre persistantes les informations des statistiques d’exécution.a runtime stats store for persisting the execution statistics information.
  • Un magasin de statistiques d’attente pour rendre persistantes les informations des statistiques d’attente.a wait stats store for persisting wait statistics information.

Le nombre de plans uniques pouvant être stockés pour une requête dans le magasin de plans est limité par l’option de configuration max_plans_per_query .The number of unique plans that can be stored for a query in the plan store is limited by the max_plans_per_query configuration option. Pour améliorer les performances, les informations sont écrites dans les magasins de façon asynchrone.To enhance performance, the information is written to the stores asynchronously. Pour optimiser l'espace, les statistiques d'exécution du runtime du magasin de statistiques du runtime sont agrégées sur une période fixe.To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed time window. Les informations contenues dans ces magasins sont visibles en interrogeant les vues de catalogue du magasin des requêtes.The information in these stores is visible by querying the Query Store catalog views.

La requête suivante retourne des informations sur les requêtes et plans du magasin des requêtes.The following query returns information about queries and plans in the Query Store.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id ;

Utiliser la fonctionnalité Requêtes régresséesUse the Regressed Queries feature

Après avoir activé le magasin des requêtes, actualisez la partie de la base de données du volet de l’Explorateur d’objets pour ajouter la section Magasin des requêtes .After enabling the Query Store, refresh the database portion of the Object Explorer pane to add the Query Store section.

Arborescence du Magasin des requêtes SQL Server 2016 dans l’Explorateur d’objets SSMS Arborescence du Magasin des requêtes SQL Server 2017 dans l’Explorateur d’objets SSMSSQL Server 2016 Query Store tree in SSMS Object Explorer SQL Server 2017 Query Store tree in SSMS Object Explorer

Sélectionnez Requêtes régressées pour ouvrir le volet Requêtes régressées dans SQL Server Management StudioSQL Server Management Studio.Select Regressed Queries to open the Regressed Queries pane in SQL Server Management StudioSQL Server Management Studio. Le volet Requêtes régressées affiche les requêtes et les plans du magasin de requêtes.The Regressed Queries pane shows you the queries and plans in the query store. Utilisez les zones de liste déroulante du haut pour filtrer les requêtes selon différents critères : Durée (ms) (par défaut), Temps processeur (ms), Lectures logiques (Ko), Écritures logiques (Ko), Lectures physiques (Ko), Temps CLR (ms), DOP, Consommation de mémoire (Ko), Nombre de lignes, Mémoire utilisée par la journalisation (Ko), Mémoire utilisée par la base de données temporaire (Ko) et Temps d’attente (ms).Use the drop-down boxes at the top to filter queries based on various criteria: Duration (ms) (Default), CPU Time (ms), Logical Reads (KB), Logical Writes (KB), Physical Reads (KB), CLR Time (ms), DOP, Memory Consumption (KB), Row Count, Log Memory Used (KB), Temp DB Memory Used (KB), and Wait Time (ms).

Sélectionnez un plan pour afficher le plan de requête sous forme graphique.Select a plan to see the graphical query plan. Des boutons sont disponibles pour afficher la requête source, forcer et désactiver l’application forcée d’un plan de requête, basculer entre les formats de grille et de graphique, comparer des plans sélectionnés (si plusieurs plans sont sélectionnés) et actualiser l’affichage.Buttons are available to view the source query, force and unforce a query plan, toggle between grid and chart formats, compare selected plans (if more than one is selected), and refresh the display.

Requêtes régressées SQL Server 2016 dans l’Explorateur d'objets SSMSSQL Server 2016 Regressed Queries in SSMS Object Explorer

Pour forcer un plan, sélectionnez une requête et un plan, puis cliquez sur Forcer le plan.To force a plan, select a query and plan, and then click Force Plan. Vous pouvez uniquement forcer des plans qui ont été enregistrés par la fonctionnalité de plan de requête et sont toujours conservés dans le cache du plan de requête.You can only force plans that were saved by the query plan feature and are still retained in the query plan cache.

Recherche de requêtes en attenteFinding waiting queries

À compter de SQL Server 2017 (14.x)SQL Server 2017 (14.x) et Azure SQL DatabaseAzure SQL Database, des statistiques d’attente par requête sur la durée sont disponibles dans le magasin des requêtes.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Azure SQL DatabaseAzure SQL Database, wait statistics per query over time are available in Query Store.

Dans le Magasin des requêtes, les types d’attente sont combinés en catégories d’attente.In Query Store, wait types are combined into wait categories. Vous trouverez dans sys.query_store_wait_stats (Transact-SQL) une correspondance entre les catégories d’attente et les types d’attente.The mapping of wait categories to wait types is available in sys.query_store_wait_stats (Transact-SQL).

Sélectionnez Statistiques d’attente des requêtes pour ouvrir le volet Statistiques d’attente des requêtes dans SQL Server Management StudioSQL Server Management Studio v18 ou version ultérieure.Select Query Wait Statistics to open the Query Wait Statistics pane in SQL Server Management StudioSQL Server Management Studio v18 or higher. Le volet Statistiques d’attente des requêtes contient un graphique à barres qui indique les principales catégories d’attente dans le Magasin des requêtes.The Query Wait Statistics pane shows you a bar chart containing the top wait categories in the Query Store. Utilisez la liste déroulante du haut pour sélectionner un critère d’agrégation pour le temps d’attente : moy, max, min, écart type et total (valeur par défaut).Use the drop-down at the top to select an aggregate criteria for the wait time: avg, max, min, std dev, and total (default).

Statistiques d'attente des requêtes SQL Server 2017 dans l’Explorateur d'objets SSMSSQL Server 2017 Query Wait Statistics in SSMS Object Explorer

Sélectionnez une catégorie d’attente en cliquant sur la barre. Un affichage détaillé de la catégorie d’attente sélectionnée apparaît.Select a wait category by clicking on the bar and a detail view on the selected wait category displays. Ce nouveau graphique à barres contient les requêtes qui ont contribué à cette catégorie d’attente.This new bar chart contains the queries that contributed to that wait category.

Vue détaillée des statistiques d'attente des requêtes SQL Server 2017 dans l’Explorateur d'objets SSMSSQL Server 2017 Query Wait Statistics detail view in SSMS Object Explorer

Utilisez la zone de liste déroulante du haut pour filtrer les requêtes en fonction de différents critères de temps d’attente pour la catégorie d’attente sélectionnée : moy, max, min, écart type et total (valeur par défaut).Use the drop-down box at the top to filter queries based on various wait time criteria for the selected wait category: avg, max, min, std dev, and total (default). Sélectionnez un plan pour afficher le plan de requête sous forme graphique.Select a plan to see the graphical query plan. Des boutons permettent d'afficher la requête source, de forcer un plan de requête et d’annuler son application forcée, ainsi que d'actualiser l'affichage.Buttons are available to view the source query, force, and unforce a query plan, and refresh the display.

Les catégories d’attente combinent différents types d’attente dans des compartiments similaires par nature.Wait categories are combining different wait types into buckets similar by nature. Différentes catégories d’attente nécessitent une analyse de suivi différente pour résoudre le problème, mais les types d’attente d’une même catégorie entraînent des expériences de résolution de problèmes très similaires à condition que la requête affectée au dessus des attentes soit l’élément manquant de la plupart de ces expériences.Different wait categories require a different follow up analysis to resolve the issue, but wait types from the same category lead to very similar troubleshooting experiences, and providing the affected query on top of waits would be the missing piece to complete the majority of such investigations successfully.

Voici quelques exemples vous permettant d’obtenir plus d’insights sur votre charge de travail avant et après l’introduction des catégories d’attente dans le Magasin des requêtes :Here are some examples how you can get more insights into your workload before and after introducing wait categories in Query Store:

Expérience précédentePrevious experience Nouvelle expérienceNew experience ActionAction
Attentes élevées de RESOURCE_SEMAPHORE par base de donnéesHigh RESOURCE_SEMAPHORE waits per database Attentes élevées de mémoire dans le Magasin des requêtes pour des requêtes spécifiquesHigh Memory waits in Query Store for specific queries Recherchez les principales requêtes consommatrices de mémoire dans le Magasin des requêtes.Find the top memory consuming queries in Query Store. Ces requêtes retardent probablement davantage la progression des requêtes affectées.These queries are probably delaying further progress of the affected queries. Utilisez l’indicateur de requête MAX_GRANT_PERCENT pour ces requêtes ou pour les requêtes concernées.Consider using MAX_GRANT_PERCENT query hint for these queries, or for the affected queries.
Attentes élevées de LCK_M_X par base de donnéesHigh LCK_M_X waits per database Attentes élevées de verrouillage dans le Magasin des requêtes pour des requêtes spécifiquesHigh Lock waits in Query Store for specific queries Vérifiez les textes de requêtes pour les requêtes affectées et identifiez les entités cibles.Check the query texts for the affected queries and identify the target entities. Recherchez dans le Magasin des requêtes d’autres requêtes modifiant la même entité, qui sont fréquemment exécutées et/ou ont une durée importante.Look in Query Store for other queries modifying the same entity, which are executed frequently and/or have high duration. Après avoir identifié ces requêtes, envisagez de changer la logique d’application pour améliorer l’accès concurrentiel, ou utilisez un niveau d’isolation moins restrictif.After identifying these queries, consider changing the application logic to improve concurrency, or use a less restrictive isolation level.
Attentes élevées de PAGEIOLATCH_SH par base de donnéesHigh PAGEIOLATCH_SH waits per database Attentes élevées d’E/S de mémoire tampon dans le Magasin des requêtes pour des requêtes spécifiquesHigh Buffer IO waits in Query Store for specific queries Recherchez les requêtes comportant un grand nombre de lectures physiques dans le Magasin des requêtes.Find the queries with a high number of physical reads in Query Store. Si elles correspondent aux requêtes avec des attentes élevées d’E/S, introduisez un index sur l’entité sous-jacente pour faire des recherches au lieu d’analyses et ainsi réduire la surcharge d’E/S des requêtes.If they match the queries with high IO waits, consider introducing an index on the underlying entity, in order to do seeks instead of scans, and thus minimize the IO overhead of the queries.
Attentes élevées de SOS_SCHEDULER_YIELD par base de donnéesHigh SOS_SCHEDULER_YIELD waits per database Attentes élevées du processeur dans le Magasin des requêtes pour des requêtes spécifiquesHigh CPU waits in Query Store for specific queries Recherchez les requêtes les plus consommatrices de processeur dans le Magasin des requêtes.Find the top CPU consuming queries in Query Store. Parmi elles, identifiez celles pour lesquelles la tendance de processeur élevé correspond aux attentes élevées de processeur pour les requêtes concernées.Among them, identify the queries for which high CPU trend correlates with high CPU waits for the affected queries. Concentrez-vous sur l’optimisation de ces requêtes : il peut y avoir une régression de plan ou peut-être un index manquant.Focus on optimizing those queries - there could be a plan regression, or perhaps a missing index.

Options de configurationConfiguration Options

Pour connaître les options disponibles pour configurer les paramètres du Magasin des requêtes, consultez les options ALTER DATABASE SET (Transact-SQL).For the available options to configure Query Store parameters, see ALTER DATABASE SET options (Transact-SQL).

Interrogez la vue sys.database_query_store_options pour déterminer les options actuelles du magasin des requêtes.Query the sys.database_query_store_options view to determine the current options of the Query Store. Pour plus d’informations sur les valeurs, consultez sys.database_query_store_options.For more information about the values, see sys.database_query_store_options.

Pour obtenir des exemples sur la définition des options à l'aide d'instructions Transact-SQLTransact-SQL , consultez Gestion des options.For examples about setting configuration options using Transact-SQLTransact-SQL statements, see Option Management.

Affichez et gérez le magasin des requêtes par le biais de Management StudioManagement Studio ou à l’aide des vues et procédures suivantes.View and manage Query Store through Management StudioManagement Studio or by using the following views and procedures.

Fonctions du Magasin des requêtesQuery Store Functions

Les fonctions facilitent les opérations avec le Magasin des requêtes.Functions help operations with the Query Store.

Affichages catalogue de magasin de requêteQuery Store Catalog Views

Les affichages catalogue présentent des informations sur le magasin de requêtes.Catalog views present information about the Query Store.

Procédures stockées du magasin de requêtesQuery Store Stored Procedures

Les procédures stockées configurent le magasin de requêtes.Stored procedures configure the Query Store.

sp_query_store_consistency_check (Transact-SQL)1sp_query_store_consistency_check (Transact-SQL)1

1 dans les scénarios extrêmes Magasin des requêtes pouvez entrer un état ERREUR en raison d’erreurs internes.1 In extreme scenarios Query Store can enter an ERROR state because of internal errors. Depuis SQL Server 2017 (14.x), le cas échéant, le Magasin des requêtes peut être récupéré via l’exécution de la procédure stockée sp_query_store_consistency_check dans la base de données affectée.Starting with SQL Server 2017 (14.x), if this happens, Query Store can be recovered by executing the sp_query_store_consistency_check stored procedure in the affected database. Pour plus d’informations sur la description de la colonne actual_state_desc, consultez sys.database_query_store_options.See sys.database_query_store_options for more details described in the actual_state_desc column description.

Principaux scénarios d’utilisationKey Usage Scenarios

Gestion des optionsOption Management

Cette section fournit des instructions sur la gestion de la fonctionnalité de magasin de requête proprement dite.This section provides some guidelines on managing Query Store feature itself.

Le magasin de requêtes est-il actuellement actif ?Is Query Store currently active?

Le Magasin des requêtes stocke ses données dans la base de données utilisateur, ceci expliquant pourquoi sa taille est limitée (configurée avec MAX_STORAGE_SIZE_MB).Query Store stores its data inside the user database and that is why it has size limit (configured with MAX_STORAGE_SIZE_MB). Si les données du magasin de requêtes atteignent cette limite, le magasin de requêtes fait passer automatiquement l'état de Lecture-écriture à Lecture seule et arrête la collecte de nouvelles données.If data in Query Store hits that limit Query Store will automatically change state from read-write to read-only and stop collecting new data.

Interrogez sys.database_query_store_options pour déterminer si le magasin de requêtes est actif et s’il collecte des statistiques d’exécution.Query sys.database_query_store_options to determine if Query Store is currently active, and whether it is currently collects runtime stats or not.

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

L’état du magasin de requêtes est déterminé par la colonne actual_state.Query Store status is determined by actual_state column. S’il diffère de l’état souhaité, la colonne readonly_reason peut vous donner plus d’informations.If it's different than the desired status, the readonly_reason column can give you more information. Quand la taille du Magasin des requêtes dépasse le quota, la fonctionnalité passe en mode read_only.When Query Store size exceeds the quota, the feature will switch to read_only mode.

Accès aux options du magasin de requêtesGet Query Store options

Pour trouver des informations détaillées sur l'état du magasin de requêtes, exécutez ce qui suit dans une base de données utilisateur.To find out detailed information about Query Store status, execute following in a user database.

SELECT * FROM sys.database_query_store_options;

Définition de l’intervalle du magasin de requêtesSetting Query Store interval

Vous pouvez remplacer l'intervalle d'agrégation des statistiques d'exécution de requête (la valeur par défaut est 60 minutes).You can override interval for aggregating query runtime statistics (default is 60 minutes).

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

Notes

Les valeurs arbitraires ne sont pas autorisées pour INTERVAL_LENGTH_MINUTES.Arbitrary values are not allowed for INTERVAL_LENGTH_MINUTES. Utilisez l’une des valeurs suivantes : 1, 5, 10, 15, 30, 60 ou 1 440 minutes.Use one of the following: 1, 5, 10, 15, 30, 60, or 1440 minutes.

La nouvelle valeur de l’intervalle est exposée par le biais de l’affichage sys.database_query_store_options .New value for interval is exposed through sys.database_query_store_options view.

Utilisation de l’espace du magasin de requêtesQuery Store space usage

Pour vérifier la taille et la limite actuelles du magasin de requête, exécutez l’instruction suivante dans la base de données utilisateur.To check current the Query Store size and limit execute the following statement in the user database.

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Si le stockage du magasin de requêtes est saturé, utilisez l'instruction suivante pour l’étendre.If the Query Store storage is full use the following statement to extend the storage.

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

Définir les options du magasin des requêtesSet Query Store options

Vous pouvez définir simultanément plusieurs options de magasin de requêtes avec l'instruction ALTER DATABASE.You can set multiple Query Store options at once with a single ALTER DATABASE statement.

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

Pour obtenir une liste complète des options de configuration, consultez Options ALTER DATABASE SET (Transact-SQL).For the full list of configuration options, see ALTER DATABASE SET Options (Transact-SQL).

Nettoyage de l’espaceCleaning up the space

Les tables internes du magasin de requêtes sont créées dans le groupe de fichiers PRIMARY lors de la création de la base de données. Cette configuration ne peut pas être modifiée ultérieurement.Query Store internal tables are created in the PRIMARY filegroup during database creation and that configuration cannot be changed later. Si vous manquez d'espace, vous pouvez effacer les anciennes données du magasin de requêtes à l'aide de l'instruction suivante.If you are running out of space you might want to clear older Query Store data by using the following statement.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Vous pouvez également effacer uniquement les données de requête ad hoc, car elles sont moins pertinentes pour les optimisations de requête et l'analyse du plan, mais utilisent autant d'espace.Alternatively, you might want to clear up only ad-hoc query data, since it is less relevant for query optimizations and plan analysis but takes up just as much space.

Supprimer les requêtes ad hocDelete ad-hoc queries

Cela vous permet de purger les requêtes ad hoc et internes du magasin des requêtes, de sorte que celui-ci ne manque pas d’espace ni ne supprime les requêtes que nous devons vraiment suivre.This purges adhoc and internal queries from the Query Store so that the Query Store does not run out of space and remove queries we really need to track.

SET NOCOUNT ON
-- This purges adhoc and internal queries from 
-- the Query Store in the current database 
-- so that the Query Store does not run out of space 
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

Vous pouvez définir votre propre procédure avec une logique différente pour effacer les données dont vous n’avez plus besoin.You can define your own procedure with different logic for clearing up data you no longer want.

L’exemple ci-dessus utilise la procédure stockée étendue sp_query_store_remove_query pour supprimer les données inutiles.The example above uses the sp_query_store_remove_query extended stored procedure for removing unnecessary data. Vous pouvez aussi utiliser :You can also use:

  • sp_query_store_reset_exec_stats pour effacer les statistiques d’exécution pour un plan donné.sp_query_store_reset_exec_stats to clear runtime statistics for a given plan.
  • sp_query_store_remove_plan pour supprimer un plan unique.sp_query_store_remove_plan to remove a single plan.

Audit et résolution des problèmes de performancesPerformance Auditing and Troubleshooting

Le magasin de requêtes conserve un historique des métriques de compilation et de runtime pour des exécutions de requêtes, ce qui vous permet de poser des questions sur votre charge de travail.Query Store keeps a history of compilation and runtime metrics throughout query executions, allowing you to ask questions about your workload.

Les n dernières requêtes exécutées sur la base de données ?Last n queries executed on the database?

SELECT TOP 10 qt.query_sql_text, q.query_id,
    qt.query_text_id, p.plan_id, rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
ORDER BY rs.last_execution_time DESC;

Nombre d’exécutions de chaque requête ?Number of executions for each query?

SELECT q.query_id, qt.query_text_id, qt.query_sql_text,
    SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text
ORDER BY total_execution_count DESC;

Nombre de requêtes avec la durée moyenne d’exécution la plus longue au cours de la dernière heure ?The number of queries with the longest average execution time within last hour?

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,
    qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,
    rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

Nombre de requêtes ayant la moyenne la plus élevée de lectures d’E/S physiques au cours des dernières 24 heures, avec le nombre moyen de lignes et d’exécutions correspondant ?The number of queries that had the biggest average physical I/O reads in last 24 hours, with corresponding average row count and execution count?

SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text,
    q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,
    rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads DESC;

Requêtes avec plusieurs plans ?Queries with multiple plans? Ces requêtes sont particulièrement intéressantes, car elles sont adaptées aux régressions dues à un changement de plan.These queries are especially interesting because they are candidates for regressions due to plan choice change. La requête suivante identifie ces requêtes, ainsi que tous les plans :The following query identifies these queries along with all plans:

WITH Query_MultPlans
AS
(
SELECT COUNT(*) AS cnt, q.query_id
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
GROUP BY q.query_id
HAVING COUNT(distinct plan_id) > 1
)

SELECT q.query_id, object_name(object_id) AS ContainingObject,
    query_sql_text, plan_id, p.query_plan AS plan_xml,
    p.last_compile_start_time, p.last_execution_time
FROM Query_MultPlans AS qm
JOIN sys.query_store_query AS q
    ON qm.query_id = q.query_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt
    ON qt.query_text_id = q.query_text_id
ORDER BY query_id, plan_id;

Requêtes ayant récemment régressé en termes de performances (en comparant avec d’autres points dans le temps) ?Queries that recently regressed in performance (comparing different point in time)? L'exemple de requête suivant retourne toutes les requêtes dont le temps d'exécution a doublé au cours des dernières 48 heures suite à un changement de plan.The following query example returns all queries for which execution time doubled in last 48 hours due to a plan choice change. La requête compare tous les intervalles de statistiques d'exécution côte à côte.Query compares all runtime stat intervals side by side.

SELECT
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    rs1.runtime_stats_id AS runtime_stats_id_1,
    rsi1.start_time AS interval_1,
    p1.plan_id AS plan_1,
    rs1.avg_duration AS avg_duration_1,
    rs2.avg_duration AS avg_duration_2,
    p2.plan_id AS plan_2,
    rsi2.start_time AS interval_2,
    rs2.runtime_stats_id AS runtime_stats_id_2
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p1
    ON q.query_id = p1.query_id
JOIN sys.query_store_runtime_stats AS rs1
    ON p1.plan_id = rs1.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi1
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
JOIN sys.query_store_plan AS p2
    ON q.query_id = p2.query_id
JOIN sys.query_store_runtime_stats AS rs2
    ON p2.plan_id = rs2.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi2
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())
    AND rsi2.start_time > rsi1.start_time
    AND p1.plan_id <> p2.plan_id
    AND rs2.avg_duration > 2*rs1.avg_duration
ORDER BY q.query_id, rsi1.start_time, rsi2.start_time;

Si vous souhaitez voir toutes les régressions de performances (pas uniquement celles liées au changement de plan), supprimez simplement la condition AND p1.plan_id <> p2.plan_id de la requête précédente.If you want to see performance all regressions (not only those related to plan choice change) than just remove condition AND p1.plan_id <> p2.plan_id from the previous query.

Quelles sont les requêtes qui attendent le plus ?Queries that are waiting the most? Cette requête retourne les 10 premières requêtes qui attendent le plus.This query will return top 10 queries that wait the most.

SELECT TOP 10
    qt.query_text_id,
    q.query_id,
    p.plan_id,
    sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
GROUP BY qt.query_text_id, q.query_id, p.plan_id
ORDER BY sum_total_wait_ms DESC

Requêtes ayant récemment régressé en termes de performances (en comparant les exécutions récentes à l’historique) ?Queries that recently regressed in performance (comparing recent vs. history execution)? La requête suivante compare l'exécution des requête en fonction des périodes d'exécution.The next query compares query execution based periods of execution. Dans cet exemple spécifique, la requête compare l’exécution pendant une période récente (1 heure) et l’exécution pendant une période historique (la veille), puis identifie celle qui a introduit additional_duration_workload.In this particular example the query compares execution in recent period (1 hour) vs. history period (last day) and identifies those that introduced additional_duration_workload. Cette mesure est calculée comme suit : différence entre la moyenne des exécutions récentes et la moyenne des exécutions historiques, multipliée par le nombre d'exécutions récentes.This metrics is calculated as a difference between recent average execution and history average execution multiplied by the number of recent executions. Elle représente en fait la durée supplémentaire introduite dans les exécutions récentes en comparaison avec les exécutions historiques :It actually represents how much of additional duration recent executions introduced compared to history:

--- "Recent" workload - last 1 hour
DECLARE @recent_start_time datetimeoffset;
DECLARE @recent_end_time datetimeoffset;
SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME());
SET @recent_end_time = SYSUTCDATETIME();

--- "History" workload
DECLARE @history_start_time datetimeoffset;
DECLARE @history_end_time datetimeoffset;
SET @history_start_time = DATEADD(hour, -24, SYSUTCDATETIME());
SET @history_end_time = SYSUTCDATETIME();

WITH
hist AS
(
    SELECT
        p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(distinct p.plan_id) AS num_plans
     FROM sys.query_store_runtime_stats AS rs
        JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
    WHERE (rs.first_execution_time >= @history_start_time
               AND rs.last_execution_time < @history_end_time)
        OR (rs.first_execution_time <= @history_start_time
               AND rs.last_execution_time > @history_start_time)
        OR (rs.first_execution_time <= @history_end_time
               AND rs.last_execution_time > @history_end_time)
    GROUP BY p.query_id
),
recent AS
(
    SELECT
        p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(distinct p.plan_id) AS num_plans
    FROM sys.query_store_runtime_stats AS rs
        JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
    WHERE  (rs.first_execution_time >= @recent_start_time
               AND rs.last_execution_time < @recent_end_time)
        OR (rs.first_execution_time <= @recent_start_time
               AND rs.last_execution_time > @recent_start_time)
        OR (rs.first_execution_time <= @recent_end_time
               AND rs.last_execution_time > @recent_end_time)
    GROUP BY p.query_id
)
SELECT
    results.query_id AS query_id,
    results.query_text AS query_text,
    results.additional_duration_workload AS additional_duration_workload,
    results.total_duration_recent AS total_duration_recent,
    results.total_duration_hist AS total_duration_hist,
    ISNULL(results.count_executions_recent, 0) AS count_executions_recent,
    ISNULL(results.count_executions_hist, 0) AS count_executions_hist
FROM
(
    SELECT
        hist.query_id AS query_id,
        qt.query_sql_text AS query_text,
        ROUND(CONVERT(float, recent.total_duration/
                   recent.count_executions-hist.total_duration/hist.count_executions)
               *(recent.count_executions), 2) AS additional_duration_workload,
        ROUND(recent.total_duration, 2) AS total_duration_recent,
        ROUND(hist.total_duration, 2) AS total_duration_hist,
        recent.count_executions AS count_executions_recent,
        hist.count_executions AS count_executions_hist
    FROM hist
        JOIN recent
            ON hist.query_id = recent.query_id
        JOIN sys.query_store_query AS q
            ON q.query_id = hist.query_id
        JOIN sys.query_store_query_text AS qt
            ON q.query_text_id = qt.query_text_id
) AS results
WHERE additional_duration_workload > 0
ORDER BY additional_duration_workload DESC
OPTION (MERGE JOIN);

Maintien de la stabilité des performances des requêtesMaintaining query performance stability

Pour les requêtes exécutées plusieurs fois, vous pouvez remarquer que SQL ServerSQL Server utilise différents plans, ce qui entraîne une utilisation des ressources et une durée différentes.For queries executed multiple times you may notice that SQL ServerSQL Server uses different plans, resulting in different resource utilization and duration. Le magasin de requêtes permet de détecter le moment où les performances des requêtes ont régressé et de déterminer le plan optimal dans un délai donné.With Query Store you can detect when query performance regressed and determine the optimal plan within a period of interest. Vous pouvez ensuite forcer l'application de ce plan optimal pour l'exécution de requêtes ultérieures.You can then force that optimal plan for future query execution.

Vous pouvez également identifier les performances de requêtes incohérentes avec des paramètres (définis manuellement ou automatiquement).You can also identify inconsistent query performance for a query with parameters (either auto-parameterized or manually parameterized). Parmi les différents plans, vous pouvez identifier le plan qui est suffisamment rapide et optimal pour la totalité ou la plupart des valeurs de paramètre et forcer ce plan, en maintenant ainsi des performances prévisibles pour un ensemble plus large de scénarios utilisateur.Among different plans you can identify the plan which is fast and optimal enough for all or most of the parameter values and force that plan, keeping predictable performance for the wider set of user scenarios.

Forcer un plan pour une requête (appliquer une stratégie de forçage)Force a plan for a query (apply forcing policy)

Quand un plan est forcé pour une requête donnée, SQL ServerSQL Server tente de forcer le plan dans l’optimiseur.When a plan is forced for a certain query, SQL ServerSQL Server tries to force the plan in the optimizer. Si le forçage de plan échoue, un XEvent est déclenché et l’optimiseur est tenu d’optimiser de façon normale.If plan forcing fails, an XEvent is fired and the optimizer is instructed to optimize in the normal way.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;

Quand vous utilisez sp_query_store_force_plan , vous pouvez uniquement forcer des plans qui ont été enregistrés par le magasin de requêtes en tant que plan pour cette requête.When using sp_query_store_force_plan you can only force plans that were recorded by Query Store as a plan for that query. En d'autres termes, les plans disponibles pour une requête sont uniquement ceux qui ont déjà été utilisés pour exécuter cette requête lorsque le magasin de requêtes était actif.In other words, the only plans available for a query are those that were already used to execute that query while Query Store was active.

Considérer l’application forcée du support de l’avance rapide et des curseurs statiques Plan forcing support for fast forward and static cursors

À compter de SQL Server 2019 (15.x)SQL Server 2019 (15.x) et d’Azure SQL Database (tous les modèles de déploiement), le Magasin des requêtes prend en charge la possibilité de forcer des plans d’exécution de requêtes pour l’avance rapide et les curseurs Transact-SQLTransact-SQL et d’API statiques.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x) and Azure SQL Database (all deployment models), Query Store supports the ability to force query execution plans for fast forward and static Transact-SQLTransact-SQL and API cursors. Le forçage est pris en charge via sp_query_store_force_plan ou via les rapports du magasin des requêtes SQL Server Management StudioSQL Server Management Studio.Forcing is supported via sp_query_store_force_plan or through SQL Server Management StudioSQL Server Management Studio Query Store reports.

Annuler l’application forcée du plan pour une requêteRemove plan forcing for a query

Pour vous appuyer à nouveau sur l’optimiseur de requête SQL ServerSQL Server pour calculer le plan de requête optimal, utilisez sp_query_store_unforce_plan pour annuler l’application forcée du plan qui était sélectionné pour la requête.To rely again on the SQL ServerSQL Server query optimizer to calculate the optimal query plan, use sp_query_store_unforce_plan to unforce the plan that was selected for the query.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;

Voir aussiSee Also