Activer et utiliser le Query Store

Le Query Store est une fonctionnalité disponible dans SQL Server depuis la version 2016, qui conserve et agrège les informations de performance des requêtes.

Activer le Query Store

Vous pouvez activer le Query Store ou vérifier que le Query Store est bien activé en utilisant les propriétés de la base de données.

Pour ouvrir la fenêtre des propriétés de la base de données, faites un clic droit sur la base de données dans SQL Server Management Studio (SSMS) et choisissez « Propriétés » ou en anglais « Properties ». Voir l’image ci-dessous.

Dans la fenêtre qui s’ouvre, cliquez sur « Query Store » dans le menu de gauche, en français, « Magasin de requêtes ».

Le query store doit être activé (Read-Write), et le mode de capture doit être « Auto ». Voir l’image ci-dessous.

Activer le Query Store par script

Cette requête peut être utilisée pour voir sur quelles bases de données le Query Store est activé sur votre instance :

-----------------------------------------------------------------
-- check is the query store is enabled on some databases.
--
-- rudi@babaluga.com, go ahead license
-----------------------------------------------------------------

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT 
	d.name,
	d.database_id,
	d.create_date,
	d.state_desc as [state]
FROM sys.databases d
WHERE d.is_query_store_on = 1
OPTION (RECOMPILE, MAXDOP 1);

Pour savoir s’il collecte vraiment, interrogez la vue sys.database_query_store_options dans la base, et vérifiez les colonnes suivantes :

• actual_state_desc : doit être à READ_WRITE ;
• query_capture_mode_desc : doit être à AUTO ;
• max_storage_size_mb devrait être à 1000 au moins pour être sûr qu’il y a cet espace dédié au Query Store pour contenir un historique suffisant au diagnostic.

Pour activer le Query Store sur une base de données par requête, utilisez la requête suivante :

-----------------------------------------------------------------------------------
-- Activate the Query Store on a database and apply recommended settings
-- (change db_name)

-- To specify the database name, use the "Specify Values for Template Parameters"
-- Navigate to Query-> Specify Values for Template Parameters.
-- Or use keyboard shortcut key Ctrl+Shift+M. 
--
-- rudi@babaluga.com, go ahead license
-----------------------------------------------------------------------------------

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

ALTER DATABASE [db_name] SET QUERY_STORE = ON
(
	OPERATION_MODE = READ_WRITE, 

	-- Data retention period (30 days by default, 60 for more history)
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),

    -- Flush to disk (900 sec = 15 min by default)
    DATA_FLUSH_INTERVAL_SECONDS = 900,

   -- Maximum storage size for Query Store
   -- Default 2019+ = 1000 MB, but 2000-5000 MB recommended in production
 	MAX_STORAGE_SIZE_MB = 2000, 

    -- Stats aggregation interval (60 min by default, 30 for more granularity)
    INTERVAL_LENGTH_MINUTES = 60,

   -- Automatic cleanup when close to the limit
    SIZE_BASED_CLEANUP_MODE = AUTO,
 
    -- Capture mode: AUTO filters trivial queries
    -- ALL = capture everything (debug), NONE = pause, CUSTOM = fine rules (2019+)
	QUERY_CAPTURE_MODE = AUTO
)
GO

Le Query Store a-t-il un impact sur les performances ?

Une crainte fréquente est que l’activation du Query Store dégrade les performances du serveur. Cette crainte n’est pas fondée. L’impact est négligeable dans la grande majorité des cas, et les informations fournies par le Query Store sont indispensables pour diagnostiquer et résoudre les problèmes de performance.

Microsoft l’active par défaut

Depuis SQL Server 2022, le Query Store est activé par défaut sur toutes les nouvelles bases de données. Une base restaurée ou migrée depuis une version antérieure conserve en revanche ses propres réglages : après une migration, vérifiez qu’il est bien actif. Il est également activé par défaut sur Azure SQL Database et Azure SQL Managed Instance.

Plusieurs fonctionnalités de l’optimiseur en dépendent : les Query Store hints, le CE feedback et le DOP feedback. Si vous craignez son coût, le mode de capture CUSTOM permet de limiter la collecte.

Une architecture asynchrone conçue pour ne pas gêner

Le Query Store a été conçu dès le départ pour minimiser son impact :

1. Écriture en mémoire d’abord : les statistiques d’exécution sont collectées en mémoire, pas sur disque.
2. Écriture asynchrone sur disque : les données en mémoire sont écrites sur disque par un processus en arrière-plan, de manière asynchrone. Les requêtes n’attendent pas cette écriture.
3. Intervalle de vidage configurable : par défaut, les statistiques sont écrites sur disque toutes les 15 minutes (DATA_FLUSH_INTERVAL_SECONDS = 900), ce qui réduit encore les opérations d’entrée/sortie.

Un overhead de 3 à 5 % dans le pire des cas

Microsoft estime l’overhead moyen à 3 à 5 %. En pratique, pour les charges de travail classiques basées sur des procédures stockées et des requêtes paramétrées, l’impact est souvent imperceptible. Seules les charges très lourdes avec beaucoup de requêtes ad hoc non paramétrées peuvent observer un impact plus marqué, ce qui est atténué par le mode de capture AUTO (par défaut depuis SQL Server 2019, ALL en 2016 et 2017) ou CUSTOM (disponible depuis SQL Server 2019).

Quand le Query Store atteint sa limite de stockage

Si le Query Store atteint sa limite de stockage (MAX_STORAGE_SIZE_MB), il bascule automatiquement en mode READ_ONLY. Il cesse alors de collecter : les requêtes continuent de s’exécuter normalement, mais vos données de diagnostic ne sont plus à jour. C’est pour éviter cette situation que le nettoyage automatique par taille (SIZE_BASED_CLEANUP_MODE = AUTO) existe.

Les bonnes pratiques pour un impact minimal

• Utilisez le mode de capture AUTO ou CUSTOM (jamais ALL en production).
• Définissez MAX_STORAGE_SIZE_MB à 1000 MB minimum.
• Gardez SIZE_BASED_CLEANUP_MODE = AUTO pour le nettoyage automatique.
• Maintenez votre instance SQL Server à jour avec les derniers correctifs cumulatifs.

Si vous n’avez pas accès au Query Store (SQL Server 2014 et antérieurs)

Vous pouvez sinon récupérer, avec les événements étendus, une trace des requêtes dont la durée dépasse une seconde : https://github.com/rudi-bruchez/tsql-scripts/blob/main/extended-events/on-prem/long-running-queries-create.sql

Pour aller plus loin

• Vidéos de sessions DataFrogs sur le Query Store.
  • vidéo : Nicolas Souquet – Tout savoir sur le Query Store.
  • vidéo : Steven Naudet