Meilleures pratiques pour la gestion du Magasin des requêtes

S’applique à : SQL Server base de données Azure SQL Azure SQL Managed Instance

Cet article décrit la gestion du Magasin des requêtes SQL Server et des fonctionnalités environnantes.

• Pour plus d’informations sur la configuration et l’administration avec le Magasin des requêtes, consultez Supervision du niveau de performance avec le Magasin des requêtes.

Magasin des requêtes par défaut dans base de données Azure SQL

Cette section décrit les paramètres optimaux de configuration par défaut dans Azure SQL Database, conçus pour garantir un fonctionnement fiable du magasin de requêtes et des fonctionnalités dépendantes. La configuration par défaut est optimisée pour la collecte des données en continu, c’est-à-dire pour passer le moins de temps possible aux états OFF/READ_ONLY. Pour plus d’informations sur toutes les options de Magasin des requêtes disponibles, consultez Options ALTER DATABASE SET (Transact-SQL).

Si vous souhaitez conserver vos paramètres personnalisés, utilisez ALTER DATABASE avec les options du magasin de requêtes pour rétablir la configuration à l’état précédent. Découvrez les meilleures pratiques liées au Magasin de données des requêtes pour savoir comment choisir des paramètres de configuration optimaux.

Définir le mode de capture optimal du Magasin des requêtes

Conservez les données les plus pertinentes dans le magasin de requêtes. Le tableau suivant décrit des scénarios standard pour chaque mode de capture du Magasin des requêtes :

Conserver les données les plus pertinentes dans le magasin des requêtes

Configurez le Magasin des requêtes afin qu’il contienne uniquement les données pertinentes. Ainsi, il s’exécutera toujours en offrant une excellente expérience de résolution des problèmes tout en ayant un impact minimal sur votre charge de travail normale.

Le tableau suivant décrit les bonnes pratiques :

Stratégies de capture personnalisées

Quand le mode Capture Magasin des requêtes PERSONNALISÉ est activé, vous pouvez affiner la collecte de données sur un serveur spécifique au moyen de configurations supplémentaires du Magasin des requêtes, disponibles sous un nouveau paramètre de stratégie de capture du Magasin des requêtes.

Les nouveaux paramètres personnalisés définissent ce qui se passe pendant la durée seuil de la stratégie de capture interne. Il s’agit d’une durée limite pendant laquelle les conditions configurables sont évaluées et, si elles ont la valeur true, la requête peut être capturée par le Magasin des requêtes.

Le mode de capture du magasin de requêtes spécifie la stratégie de capture de requêtes du magasin de requêtes.

• Tout : capture toutes les requêtes. Cette option est la valeur par défaut dans SQL Server 2016 (13.x) et SQL Server 2017 (14.x).
• Auto : les requêtes peu fréquentes et les requêtes dont la durée de compilation et d’exécution n’est pas significative sont ignorées. Les seuils concernant le nombre d’exécutions, la durée de compilation et la durée d’exécution sont déterminés en interne. À partir de SQL Server 2019 (15.x), il s’agit de la valeur par défaut.
• Aucune : le Magasin des requêtes cesse de capturer les nouvelles requêtes.
• Personnaliser : permet un contrôle supplémentaire et permet d’ajuster la stratégie de collecte des données. Les nouveaux paramètres personnalisés définissent ce qui se passe pendant la durée seuil de la stratégie de capture interne. Il s’agit d’une durée limite pendant laquelle les conditions configurables sont évaluées et, si elles ont la valeur true, la requête peut être capturée par le Magasin des requêtes.

Le réglage d’une stratégie de capture personnalisée appropriée pour votre environnement doit être pris en compte lorsque :

• La base de données est très volumineuse.
• La base de données a un grand nombre de requêtes ad hoc uniques.
• La base de données présente des limitations spécifiques en matière de taille ou de croissance.

Exemples de stratégies de capture personnalisées

L’exemple suivant définit QUERY_CAPTURE_MODE sur AUTO et définit un mode de capture personnalisé. Chacun des éléments suivants définit les stratégies de capture personnalisées sur sa valeur par défaut dans SQL Server 2022 (16.x). Envisagez d’ajuster ces valeurs pour réduire le nombre de requêtes capturées et, par conséquent, réduire l’empreinte sur disque du Magasin des requêtes. Il est recommandé de modifier progressivement ces valeurs par petits incréments.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

L’exemple de requête suivant modifie un Magasin des requêtes existante pour utiliser une stratégie de capture personnalisée qui remplace les paramètres par défaut pour EXECUTION_COUNT et TOTAL_COMPILE_CPU_TIME_MS.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

Taille maximale du Magasin des requêtes

La valeur de taille maximale par défaut du Magasin des requêtes est de 1000 Mo, à partir de SQL Server 2019 (15.x). Dans les versions précédentes, la valeur par défaut était de 100 Mo. L’augmentation de la limite de taille maximale du Magasin des requêtes est appropriée dans une base de données occupée avec de nombreux plans de requête uniques. L’ajustement de la stratégie de capture (voir la section précédente) est une considération plus importante pour limiter la taille sur disque du Magasin des requêtes et empêcher le Magasin des requêtes d’entrer en mode READ_ONLY. Pendant que le Magasin des requêtes collecte des requêtes, des plans d’exécution et des statistiques, sa taille dans la base de données croît jusqu’à atteindre cette limite. Quand cela se produit, le magasin de requêtes change automatiquement de mode d’opération pour passer en READ_ONLY et cesse de collecter les nouvelles données, ce qui signifie que l’analyse de vos performances n’est plus précise.

• Dans SQL Server et Azure SQL Managed Instance, la limite de MAX_STORAGE_SIZE_MB n’est pas strictement appliquée.
• Dans base de données Azure SQL, la valeur maximale autorisée MAX_STORAGE_SIZE_MB est de 10 240 Mo.

La taille de stockage est vérifiée seulement quand le Magasin des requêtes écrit des données sur le disque. Cet intervalle est défini par l’option DATA_FLUSH_INTERVAL_SECONDS ou par l’option de la boîte de dialogue Magasin des requêtes de Management Studio Intervalle de vidage des données.

• La valeur par défaut de l’intervalle est 900 secondes (ou 15 minutes).
• Si le Magasin des requêtes a enfreint la limite de MAX_STORAGE_SIZE_MB entre des vérifications de la taille de stockage, il passe en mode lecture seule.
• Si l’option SIZE_BASED_CLEANUP_MODE est activée, le mécanisme de nettoyage permettant d’appliquer la limite MAX_STORAGE_SIZE_MB est également déclenché.
  • Une fois que suffisamment d’espace a été libéré, le mode Magasin des requêtes revient automatiquement en READ_WRITE.

Pour plus d’informations, consultez Options SET d’ALTER DATABASE MAX_STORAGE_SIZE_MB.

Intervalle de vidange des données (minutes)

L’intervalle de vidage des données définit la fréquence avant que les statistiques d’exécution collectées soient conservées sur le disque. Dans SQL Server Management Studio, la valeur est exprimée en minutes mais, dans Transact-SQL, elle est exprimée en secondes. La valeur par défaut est de 15 minutes (900 secondes).

• L’augmentation de l’intervalle de vidage des données peut réduire l’impact global des E/S de stockage du Magasin des requêtes, mais la charge de travail d’E/S de stockage est plus volumineuse, avec moins d’impact sur l’utilisation du disque. Utilisez une valeur plus élevée si votre charge de travail ne génère pas de grandes quantités de requêtes et de plans différents, ou si vous pouvez supporter une durée de conservation des données plus élevée avant un arrêt de la base de données.
• La diminution de l’intervalle de vidage des données diminue la quantité de données du Magasin des requêtes qui seraient perdues en cas d’arrêt, de perte d’alimentation ou de basculement. Il peut également faciliter l’impact des E/S de stockage à partir du Magasin des requêtes en écrivant sur le disque plus souvent, mais avec moins de données.

Modifier les valeurs par défaut du Magasin des requêtes

Configurez le magasin de requêtes en fonction de votre charge de travail et selon vos besoins en matière de résolution des problèmes de performances. Les paramètres par défaut sont assez bons pour démarrer, mais vous devez surveiller le comportement du Magasin des requêtes au fil du temps et ajuster sa configuration en conséquence.

Afficher les paramètres actuels du Magasin des requêtes

Affichez les paramètres de Magasin des requêtes actuels dans SQL Server Management Studio (SSMS) ou T-SQL.

USE [QueryStoreDB];
GO

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

Le script suivant définit une nouvelle valeur pour Taille maximale (Mo) :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

Utilisez SQL Server Management Studio ou Transact-SQL pour définir une valeur différente pour Intervalle de vidage des données :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

Intervalle de collecte des statistiques : définit le niveau de granularité des statistiques d’exécution collectées, exprimé en minutes. La valeur par défaut est de 60 minutes. Envisagez d’utiliser une valeur inférieure si vous avez besoin d’une précision plus fine ou de moins de temps pour détecter et atténuer les problèmes. Gardez à l’esprit que la valeur affecte directement la taille des données du Magasin des requêtes. Utilisez SQL Server Management Studio ou Transact-SQL pour définir une autre valeur pour Intervalle de collecte des statistiques :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

Seuil de requêtes périmées (jours) : stratégie de nettoyage basé sur la durée qui permet de contrôler la période de rétention des statistiques d’exécution persistantes et des requêtes inactives, exprimées en jours. Par défaut, le Magasin des requêtes est configuré pour conserver les données pendant 30 jours, ce qui est peut-être inutilement long pour votre scénario.

Évitez de conserver les données d’historique que vous ne prévoyez pas d’utiliser. Cette pratique réduit les passages à l’état lecture seule. La taille des données du Magasin des requêtes et le temps de détection et d’atténuation des problèmes seront plus prévisibles. Utilisez Management Studio ou le script suivant pour configurer la stratégie de nettoyage basée sur la durée :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

Mode de nettoyage basé sur la taille : indique si le nettoyage de données automatique aura lieu dès que la taille des données du magasin de requêtes s’approchera de la limite. Activez le nettoyage basée sur la taille pour vous assurer que le Magasin des requêtes s’exécutera toujours en mode lecture-écriture et collectera les données les plus récentes. Il n’y a aucune garantie sous des charges de travail lourdes que le nettoyage du magasin des requêtes maintiendra constamment la taille des données dans la limite. Il est possible que le nettoyage automatique des données passe en arrière-plan et bascule (temporairement) en mode lecture seule.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

Mode de capture du magasin de requêtes : spécifie la stratégie de capture de requêtes du magasin de requêtes.

• Tout : capture toutes les requêtes. Cette option est la valeur par défaut dans SQL Server 2016 (13.x) et SQL Server 2017 (14.x).
• Auto : les requêtes peu fréquentes et les requêtes dont la durée de compilation et d’exécution n’est pas significative sont ignorées. Les seuils concernant le nombre d’exécutions, la durée de compilation et la durée d’exécution sont déterminés en interne. À partir de SQL Server 2019 (15.x), il s’agit de la valeur par défaut.
• Aucune : le Magasin des requêtes cesse de capturer les nouvelles requêtes.
• Personnaliser : permet un contrôle supplémentaire et permet d’ajuster la stratégie de collecte des données. Les nouveaux paramètres personnalisés définissent ce qui se passe pendant la durée seuil de la stratégie de capture interne. Il s’agit d’une durée limite pendant laquelle les conditions configurables sont évaluées et, si elles ont la valeur true, la requête peut être capturée par le Magasin des requêtes.

Le script suivant définit QUERY_CAPTURE_MODE sur AUTO :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Exemples

L’exemple suivant définit QUERY_CAPTURE_MODE sur AUTO et définit d’autres options recommandées dans SQL Server 2016 (13.x) :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

L’exemple suivant définit QUERY_CAPTURE_MODE sur AUTO et définit d’autres options recommandées dans SQL Server 2017 (14.x) pour inclure des statistiques d’attente :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

L’exemple suivant définit la stratégie de capture PERSONNALISÉE sur les valeurs par défaut de SQL Server 2019 (15.x), au lieu du nouveau mode de capture AUTOMATIQUE par défaut. Pour plus d’informations sur les options de stratégie de capture personnalisée et les valeurs par défaut, consultez <query_capture_policy_option_list>.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Maintenance du Magasin des requêtes

Cette section fournit des instructions sur la gestion de la fonctionnalité de magasin de requête proprement dite.

État du Magasin des requêtes

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). 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.

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.

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. S’il diffère de l’état souhaité, la colonne readonly_reason peut vous donner plus d’informations. Lorsque la taille du Magasin des requêtes dépasse le quota, la fonctionnalité passe en mode lecture seule et fournit une raison. Pour obtenir des informations sur les raisons, consultez sys.database_query_store_options.

Accès aux options du magasin de requêtes

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.

SELECT * FROM sys.database_query_store_options;

Définir l’intervalle du Magasin des requêtes

Vous pouvez remplacer l'intervalle d'agrégation des statistiques d'exécution de requête (la valeur par défaut est 60 minutes). La nouvelle valeur de l'intervalle est exposée via l'affichage sys.database_query_store_options.

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

Les valeurs arbitraires ne sont pas autorisées pour INTERVAL_LENGTH_MINUTES. Utilisez l’un des intervalles suivants : 1, 5, 10, 15, 30, 60 ou 1440 minutes.

Utilisation de l’espace du magasin de requêtes

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.

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.

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

Définir les options du magasin des requêtes

Vous pouvez définir simultanément plusieurs options de magasin de requêtes avec l'instruction ALTER DATABASE.

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).

Nettoyer l’espace

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. Si vous manquez d’espace, vous pouvez effacer les anciennes données du magasin des requêtes à l’aide de l’instruction suivante.

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.

Dans Azure Synapse Analytics, l’effacement du Magasin des requêtes n’est pas disponible. Les données des sept derniers jours sont automatiquement conservées.

Supprimer les requêtes ad hoc

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.

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.

L’exemple précédent utilise la procédure stockée étendue sp_query_store_remove_query pour supprimer les données inutiles. Vous pouvez également :

• Utilisez sp_query_store_reset_exec_stats pour effacer les statistiques d’exécution d’un plan donné.
• Utilisez sp_query_store_remove_plan pour supprimer un plan unique.

Contenu connexe

• Options ALTER DATABASE SET (Transact-SQL)
• Affichages catalogue du Magasin des requêtes (Transact-SQL)
• Procédures stockées du Magasin des requêtes (Transact-SQL)
• Utilisation du Magasin des requêtes avec l’OLTP en mémoire
• Guide d’architecture de traitement des requêtes
• Indicateurs du Magasin des requêtes
• Superviser le niveau de performance avec le Magasin des requêtes
• Régler les performances avec le Magasin des requêtes
• Analyse et stockage des requêtes historiques dans Azure Synapse Analytics