Meilleures pratiques pour la gestion du Magasin des requêtes

S’applique à :SQL Serverbase de données Azure SQLAzure 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.

Paramètres par défaut de Magasin des requêtes dans Azure SQL Database

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 ALTER DATABASE SET les options (Transact-SQL).

Si vous souhaitez conserver vos paramètres personnalisés, utilisez ALTER DATABASE Magasin des requêtes options 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 de 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 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 limite temporelle au cours de laquelle les conditions configurables sont évaluées et, si l’une d’elles est vraie, la requête peut être capturée par Magasin des requêtes.

Le Magasin des requêtes Capture Mode spécifie la stratégie de capture des requêtes pour Magasin des 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.
• Aucun : Magasin des requêtes cesse de capturer de 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 limite temporelle au cours de laquelle les conditions configurables sont évaluées et, si l’une d’elles est vraie, la requête peut être capturée par 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 données de requête

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 Intervalle de vidage des données de la boîte de dialogue Magasin des requêtes de Management Studio.

• La valeur par défaut de l’intervalle est 900 secondes (ou 15 minutes).
• Si le Magasin des requêtes dépasse la limite de MAX_STORAGE_SIZE_MB entre deux 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 qu’un espace suffisant a été libéré, le mode Magasin des requêtes repasse automatiquement en mode READ_WRITE.

Pour plus d’informations, consultez ALTER DATABASE SET OPTION 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 de Magasin des requêtes, mais rendre la charge de travail des E/S de stockage plus irrégulière, avec des impacts moins fréquents mais plus importants 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.
• Réduire l’intervalle de vidage des données réduit la quantité de données de Magasin des requêtes susceptible d’être perdue lors d’un arrêt, d’une panne de courant ou d’un basculement. Il peut également atténuer l’impact des E/S de stockage de Magasin des requêtes en effectuant des écritures 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 de 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 des requêtes périmées (jours) : stratégie de nettoyage basée sur la durée qui contrôle la période de rétention des statistiques d’exécution stockées de manière persistante et des requêtes inactives, exprimée 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 en fonction de la taille afin de garantir que Magasin des requêtes fonctionne toujours en mode lecture-écriture et collecte les données les plus récentes. En cas de charges de travail élevées, rien ne garantit que le nettoyage de Magasin des requêtes maintiendra systématiquement la taille des données en dessous de 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 de Magasin des requêtes : spécifie la stratégie de capture des requêtes pour Magasin des 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.
• Aucun : Magasin des requêtes cesse de capturer de 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 limite temporelle au cours de laquelle les conditions configurables sont évaluées et, si l’une d’elles est vraie, la requête peut être capturée par 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 de 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 de Magasin des requêtes atteignent cette limite, Magasin des requêtes passe automatiquement de l’état lecture-écriture à l’état lecture seule et cesse de collecter 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.

Obtenir les options de Magasin des 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 de Magasin des requêtes

Pour vérifier la taille actuelle et la limite du Magasin des requêtes, 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>);

Configurer les options de Magasin des requêtes

Vous pouvez définir plusieurs options de Magasin des requêtes à la fois avec une seule ALTER DATABASE instruction.

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 la liste complète des options de configuration, consultez ALTER DATABASE SET Options (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 purge les requêtes ad hoc et internes du Magasin des requêtes afin que celui-ci ne soit pas à court d’espace et ne supprime pas 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

• ALTER DATABASE SET options (Transact-SQL)
• Vues de catalogue Magasin des requêtes (Transact-SQL)
• Procédures stockées de Magasin des requêtes (Transact-SQL)
• Utiliser Magasin des requêtes avec OLTP en mémoire
• Guide d’architecture de traitement des requêtes
• Indications de requête de Magasin des requêtes
• Superviser le niveau de performance avec le Magasin des requêtes
• Optimiser les performances avec Magasin des requêtes
• Analyse et stockage des requêtes historiques dans Azure Synapse Analytics