Meilleures pratiques pour la gestion des Magasin des requêtes

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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

Remarque

Dans SQL Server 2022 (16.x), Magasin des requêtes est désormais activé par défaut pour toutes les bases de données SQL Server nouvellement créées pour mieux suivre l’historique des performances, résoudre les problèmes liés au plan de requête et activer les nouvelles fonctionnalités du processeur de requêtes.

Magasin des requêtes valeurs par défaut 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 Options ALTER DATABASE SET (Transact-SQL).

Configuration Description Default Commentaire
MAX_STORAGE_SIZE_MB Spécifie la limite d’espace de données que le magasin de requêtes peut inclure dans la base de données client 100 avant SQL Server 2019 (15.x)
1000 à partir de SQL Server 2019 (15.x)		 Appliqué aux nouvelles bases de données
INTERVAL_LENGTH_MINUTES Définit la durée pendant laquelle les statistiques d’exécution collectées pour les plans de requête sont agrégées et rendues persistantes. Chaque plan de requête actif dispose au maximum d’une ligne pour une période définie avec cette configuration 60 Appliqué aux nouvelles bases de données
STALE_QUERY_THRESHOLD_DAYS Stratégie de nettoyage basée sur la durée, et qui contrôle la période de rétention des statistiques d’exécution persistantes et des requêtes inactives 30 Appliqué aux nouvelles bases de données et aux bases de données ayant la valeur par défaut précédente (367)
SIZE_BASED_CLEANUP_MODE Indique si un nettoyage automatique des données a lieu lorsque la taille des données du magasin de requêtes approche de la limite AUTO Appliqué à toutes les bases de données
QUERY_CAPTURE_MODE Indique si toutes les requêtes sont suivies, ou seulement un sous-ensemble de requêtes AUTO Appliqué à toutes les bases de données
DATA_FLUSH_INTERVAL_SECONDS Indique la durée maximale pendant laquelle les statistiques d’exécution capturées sont conservées dans la mémoire, avant le vidage sur disque 900 Appliqué aux nouvelles bases de données

Important

Ces valeurs par défaut sont automatiquement appliquées à la dernière étape de l’activation de Magasin des requêtes dans une base de données Azure SQL. Une fois activé, Azure SQL Database ne modifie pas les valeurs de configuration définies par les clients, sauf si elles affectent négativement la charge de travail principale ou les opérations fiables de l’Magasin des requêtes.

Remarque

Le Magasin des requêtes ne peut pas être désactivé dans la base de données unique Azure SQL Database et le pool élastique. L’exécution de ALTER DATABASE [database] SET QUERY_STORE = OFF retourne l’avertissement 'QUERY_STORE=OFF' is not supported in this version of SQL Server.

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 :

Mode de capture du magasin des requêtes Scénario
Tous Analysez minutieusement votre charge de travail, c’est-à-dire toutes les formes de requêtes, leur fréquence d’exécution et les autres statistiques.

Identifiez les nouvelles requêtes dans votre charge de travail.

Détectez si des requêtes ad hoc sont utilisées pour identifier les opportunités de paramétrage défini par l’utilisateur ou automatique.

Remarque : il s’agit du mode de capture par défaut dans SQL Server 2016 (13.x) et SQL Server 2017 (14.x).
Automatique Concentrez-vous sur les requêtes pertinentes et actionnables. Les requêtes qui s’exécutent régulièrement ou qui consomment beaucoup de ressources en sont un exemple.

Remarque : Dans SQL Server 2019 (15.x) et versions ultérieures, il s’agit du mode de capture par défaut.
Aucun Vous avez déjà capturé le jeu de requêtes que vous vouliez surveiller dans le runtime et souhaitez éliminer les confusions que pourraient provoquer les autres requêtes.

L’option Aucun est adaptée aux environnements de test et d’évaluation.

Elle est aussi appropriée pour les éditeurs de logiciels qui proposent le magasin de requêtes avec une configuration destinée à surveiller la charge de travail de leur application.

Cette option doit être utilisée avec précaution, car vous risquez de ne pas pouvoir suivre et optimiser de nouvelles requêtes importantes. Évitez d’utiliser l’option Aucun(e) sauf si l’un de vos scénarios l’exige.
Personnalisée SQL Server 2019 (15.x) a introduit un mode de capture personnalisé sous la ALTER DATABASE ... SET QUERY_STORE commande. Bien que l’auto soit par défaut et recommandé, s’il existe toujours des préoccupations concernant la surcharge Magasin des requêtes peuvent être introduites, les administrateurs de base de données peuvent utiliser des stratégies de capture personnalisées pour optimiser davantage le comportement de capture Magasin des requêtes. Pour plus d’informations et de recommandations, consultez les stratégies de capture personnalisée plus loin dans cet article. Pour plus d’informations sur cette syntaxe, consultez options ALTER DATABASE SET.

Remarque

Les curseurs, les requêtes dans les procédures stockées et les requêtes compilées en mode natif sont toujours capturés quand le mode de capture du Magasin des requêtes est défini sur Tous, Auto ou Personnalisé. Pour capturer des requêtes compilées en mode natif, activez la collecte des statistiques par requête avec sys.sp_xtp_control_query_exec_stats.

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 :

Meilleure pratique Setting
Limiter la conservation des données d’historique. Configurez la stratégie basée sur la durée pour activer le nettoyage automatique.
Filtrer les requêtes non pertinentes. Configurez l’option Mode de capture du Magasin des requêtes sur Auto.
Supprimer les requêtes les moins pertinentes quand la taille maximale est atteinte. Activez la stratégie de nettoyage basée sur la taille.

Stratégies de capture personnalisées

Lorsque le mode de capture Magasin des requêtes PERSONNALISÉ est activé, des configurations supplémentaires Magasin des requêtes sont disponibles sous un nouveau paramètre de stratégie de capture Magasin des requêtes pour affiner la collecte de données sur un serveur spécifique.

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 Magasin des requêtes spécifie la stratégie de capture de requête 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 et requêtes peu fréquentes dont la compilation et la durée d’exécution 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. À compter de SQL Server 2019 (15.x), il s’agit de l’option par défaut.
  • Aucun : Magasin des requêtes arrête la capture de nouvelles requêtes.
  • Personnalisé : permet un contrôle supplémentaire et la possibilité d’affiner la stratégie de collecte de 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.

Utiliser la dernière version de SQL Server Management Studio (SSMS)

Pour afficher les paramètres actuels dans Management Studio :

  1. Dans l’Explorateur d’objets SQL Server Management Studio, cliquez avec le bouton droit sur la base de données.
  2. Sélectionnez Propriétés.
  3. Sélectionnez Magasin des requêtes. Dans la page Magasin des requêtes, vérifiez que le mode d’opération (demandé) est en lecture-écriture.
  4. Modifiez Magasin des requêtes mode de capture en mode personnalisé.
  5. Notez que les quatre champs de stratégie de capture sous Magasin des requêtes stratégie de capture sont désormais activés et configurables.

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’encombrement 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 une 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 Magasin des requêtes

La valeur de taille maximale par défaut du Magasin des requêtes est de 1 000 Mo, à partir de SQL Server 2019 (15.x). Dans les versions précédentes, la valeur par défaut était 100 Mo. L’augmentation de la limite de taille maximale de la 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 l’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. Lorsque cela se produit, Magasin des requêtes modifie automatiquement le mode d’opération en READ_ONLY et cesse de collecter de nouvelles données, ce qui signifie que votre analyse des performances n’est plus précise.

  • Dans SQL Server et Azure SQL Managed Instance, MAX_STORAGE_SIZE_MB la limite n’est pas strictement appliquée.
  • Dans Azure SQL Database, 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 dépassé la limite entre la MAX_STORAGE_SIZE_MB taille de stockage case activée s, elle 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é effacé, le mode Magasin des requêtes revient automatiquement en mode READ_WRITE.

Pour plus d’informations, consultez ALTER DATABASE SET OPTION MAX_STORAGE_SIZE_Mo.

Intervalle de vidage 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 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 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 de Magasin des requêtes en écrivant sur le disque plus souvent, mais avec moins de données.

Remarque

L’indicateur de trace 7745 empêche l’écriture sur le disque des données du Magasin des requêtes en cas de commande d’arrêt ou de basculement. Pour plus d’informations, consultez Utiliser Magasin des requêtes dans les serveurs stratégiques.

Modifier les valeurs par défaut 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 Magasin des requêtes paramètres actuels

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

Utiliser la dernière version de SQL Server Management Studio (SSMS)

Pour afficher les paramètres actuels dans Management Studio :

  1. Dans l’Explorateur d’objets SQL Server Management Studio, cliquez avec le bouton droit sur la base de données.
  2. Sélectionnez Propriétés.
  3. Sélectionnez Magasin des requêtes.

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é pour la statistique d’exécution collectée, exprimée 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ête obsolète (jours) : stratégie de propre up basée sur le temps qui contrôle 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 propre up basée sur le temps :

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

Mode de nettoyage basé sur la taille : spécifie si les données automatiques propre up ont lieu lorsque Magasin des requêtes taille des données approche 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’existe aucune garantie sous des charges de travail lourdes qui Magasin des requêtes propre up conservent constamment la taille des données sous 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);

Magasin des requêtes mode de capture : spécifie la stratégie de capture de requête 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 et requêtes peu fréquentes dont la compilation et la durée d’exécution 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. À compter de SQL Server 2019 (15.x), il s’agit de l’option par défaut.
  • Aucun : Magasin des requêtes arrête la capture de nouvelles requêtes.
  • Personnalisé : permet un contrôle supplémentaire et la possibilité d’affiner la stratégie de collecte de 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.

Important

Les curseurs, les requêtes dans les procédures stockées et les requêtes compilées en mode natif sont toujours capturés quand le mode de capture du Magasin des requêtes est défini sur Tous, Auto ou Personnalisé. Pour capturer des requêtes compilées en mode natif, activez la collecte des statistiques par requête avec sys.sp_xtp_control_query_exec_stats.

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 les 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 de 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 plus d’informations sur des 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.

Remarque

Pour Azure Synapse Analytics, la personnalisation des options de configuration du Magasin des requêtes, comme illustré dans cette section, n’est pas prise en charge.

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 données plus anciennes 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 des requêtes et l’analyse de plan, mais occupent autant d’espace.

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

Supprimer des requêtes ad hoc

Cela vide les requêtes ad hoc et internes de l’Magasin des requêtes afin que le Magasin des requêtes ne s’exécute pas d’espace et supprime les requêtes dont nous avons vraiment besoin pour effectuer le suivi.

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 sp_query_store_remove_query procédure stockée étendue pour supprimer des 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