Régler les performances avec le Magasin des requêtes
S’applique à :
SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics
La fonctionnalité de Magasin des requêtes SQL Server vous permet de découvrir et de régler les requêtes dans votre charge de travail via l’interface visuelle SQL Server Management Studio et les requêtes T-SQL. Cet article explique comment prendre des informations exploitables pour améliorer les performances des requêtes dans votre base de données, notamment comment identifier les requêtes en fonction de leurs statistiques d’utilisation et forcer des plans. Vous pouvez également utiliser la fonctionnalité d’indicateurs de Magasin des requêtes pour identifier les requêtes et mettre en forme leurs plans de requête sans modifier le code de l’application.
- Pour plus d’informations sur la façon dont ces données sont collectées, consultez Comment Magasin des requêtes collecte des données.
- 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.
- Pour plus d’informations sur l’utilisation du magasin de requêtes dans Azure SQL Database, consultez Utilisation du magasin de requêtes dans Azure SQL Database.
Exemples de requêtes en vue de l’optimisation des performances
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.
Les exemples de requêtes suivants peuvent être utiles dans votre base de référence de performances et l’investigation des performances des requêtes :
Dernières requêtes exécutées sur la base de données
Dernières requêtes exécutées sur la base de données au cours de la dernière heure :
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
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER 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.last_execution_time DESC;
Nombre d’exécutions
Nombre d’exécutions pour chaque requête au cours de la dernière heure :
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
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY q.query_id,
qt.query_text_id,
qt.query_sql_text
ORDER BY total_execution_count DESC;
Durée moyenne d’exécution la plus longue
Nombre de requêtes dont la durée moyenne est la plus élevée au cours de la dernière heure :
SELECT TOP 10 ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) /
NULLIF(SUM(rs.count_executions), 0), 2) avg_duration,
SUM(rs.count_executions) AS total_execution_count,
qt.query_sql_text,
q.query_id,
qt.query_text_id,
p.plan_id,
GETUTCDATE() AS CurrentUTCTime,
MAX(rs.last_execution_time) AS last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_sql_text,
q.query_id,
qt.query_text_id,
p.plan_id
ORDER BY avg_duration DESC;
Lectures d’E/S physiques moyennes les plus élevées
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 :
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
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
INNER 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
Les requêtes avec plusieurs plans sont particulièrement intéressantes, car elles peuvent être candidates à une régression des performances en raison d’un changement de choix de plan.
La requête suivante identifie les requêtes avec le plus grand nombre de plans au cours de la dernière heure :
SELECT q.query_id,
object_name(object_id) AS ContainingObject,
COUNT(*) AS QueryPlanCount,
STRING_AGG(p.plan_id, ',') plan_ids,
qt.query_sql_text
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY OBJECT_NAME(object_id),
q.query_id,
qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY QueryPlanCount DESC;
La requête suivante identifie ces requêtes ainsi que tous les plans au cours de la dernière heure :
WITH Query_MultPlans
AS (
SELECT COUNT(*) AS QueryPlanCount,
q.query_id
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER 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,
p.plan_id,
p.query_plan AS plan_xml,
p.last_compile_start_time,
p.last_execution_time
FROM Query_MultPlans AS qm
INNER JOIN sys.query_store_query AS q
ON qm.query_id = q.query_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_query_text qt
ON qt.query_text_id = q.query_text_id
INNER 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 q.query_id,
p.plan_id;
Délais d’attente les plus élevés
Cette requête retourne les 10 premières requêtes avec les durées d’attente les plus élevées pour la dernière heure :
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
INNER JOIN sys.query_store_plan p
ON ws.plan_id = p.plan_id
INNER JOIN sys.query_store_query q
ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_text_id,
q.query_id,
p.plan_id
ORDER BY sum_total_wait_ms DESC;
Remarque
Dans Azure Synapse Analytics, les exemples de requêtes Magasin des requêtes de cette section sont pris en charge à l’exception des statistiques d’attente, qui ne sont pas disponibles dans azure Synapse Analytics Magasin des requêtes DMV.
Requêtes dont les performances ont récemment régressé
L’exemple de requête suivant retourne toutes les requêtes pour lesquelles le temps d’exécution a doublé au cours des 48 dernières heures en raison d’un changement de choix de plan. Cette requête compare tous les intervalles de statistiques d’exécution côte à côte :
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
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p1
ON q.query_id = p1.query_id
INNER JOIN sys.query_store_runtime_stats AS rs1
ON p1.plan_id = rs1.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi1
ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p2
ON q.query_id = p2.query_id
INNER JOIN sys.query_store_runtime_stats AS rs2
ON p2.plan_id = rs2.plan_id
INNER 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 afficher toutes les régressions de performances (non seulement les régressions liées au changement de choix de plan), supprimez la condition AND p1.plan_id <> p2.plan_id de la requête précédente.
Requêtes avec régression historique des performances
Lorsque vous souhaitez comparer l’exécution récente à l’exécution historique, la requête suivante compare l’exécution de requête en fonction de la période d’exécution. Dans cet exemple spécifique, la requête compare l’exécution pendant une période récente (il y a 1 heure) et l’exécution pendant une période historique (la veille), puis identifie celle qui a introduit 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. Il représente la durée supplémentaire de ces exécutions récentes introduites, par rapport à l’historique :
--- "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
INNER 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
INNER 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
INNER JOIN recent
ON hist.query_id = recent.query_id
INNER JOIN sys.query_store_query AS q
ON q.query_id = hist.query_id
INNER 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);
Maintenir la stabilité des performances des requêtes
Pour les requêtes exécutées plusieurs fois, vous remarquerez peut-être que SQL Server utilise différents plans, ce qui entraîne une utilisation et une durée différentes des ressources. Le Magasin des 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é. Vous pouvez ensuite forcer l'application de ce plan optimal pour l'exécution de requêtes ultérieures.
Vous pouvez également identifier les performances de requête incohérentes pour une requête avec des paramètres (paramétrables automatiquement ou manuellement). Parmi les différents plans, vous pouvez identifier le plan qui est suffisamment rapide et optimal pour toutes ou la plupart des valeurs de paramètre et forcer ce plan. Cela permet de maintenir des performances prévisibles pour l’ensemble plus large de scénarios utilisateur.
Forcer un plan pour une requête (appliquer une stratégie de forçage)
Lorsqu’un plan est forcé pour une requête spécifique, SQL Server tente de forcer le plan dans l’optimiseur. Si le forçage de plan échoue, un événement étendu est déclenché et l’optimiseur est invité à optimiser de la façon normale.
EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;
Lorsque vous utilisezsp_query_store_force_plan, vous ne pouvez forcer les plans enregistrés que par Magasin des requêtes comme plan pour cette requête. En d’autres termes, les seuls plans disponibles pour une requête sont des plans qui ont déjà été utilisés pour exécuter cette requête alors que Magasin des requêtes était actif.
Remarque
Forcer les plans dans Magasin des requêtes n’est pas pris en charge dans Azure Synapse Analytics.
Prise en charge de la possibilité de forcer le plan pour l’avance rapide et les curseurs statiques
Dans SQL Server 2019 (15.x) et versions ultérieures, et Azure SQL Database (tous les modèles de déploiement), Magasin des requêtes prend en charge la possibilité de forcer les plans d’exécution de requête pour des curseurs Transact-SQL et API statiques. Le forçage est pris en charge via ou via sp_query_store_force_plan des rapports Magasin des requêtes SQL Server Management Studio.
Annuler l’application forcée du plan pour une requête
Pour vous appuyer à nouveau sur l’optimiseur de requête SQL Server pour calculer le plan de requête optimal, utilisez cette option sp_query_store_unforce_plan pour annuler l’application du plan sélectionné pour la requête.
EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;
Contenu connexe
- Surveillance des performances à l’aide du magasin des requêtes
- Bonnes pratiques relatives au Magasin des requêtes
- Utilisation du Magasin des requêtes avec l’OLTP en mémoire
- Scénarios d’utilisation du magasin de requêtes
- Comment le Magasin des requêtes collecte les données
- Procédures stockées du Magasin des requêtes (Transact-SQL)
- Affichages catalogue du Magasin des requêtes (Transact-SQL)
- Ouvrir le Moniteur d’activité (SQL Server Management Studio)
- Statistiques des requêtes dynamiques
- Moniteur d’activité
- sys.database_query_store_options (Transact-SQL)
- Surveillance et réglage des performances
- Outils de surveillance et de réglage des performances
- Indicateurs du Magasin des requêtes
- Paramétrage de la base de données à l’aide de la charge de travail à partir de Magasin des requêtes avec l’Assistant Paramétrage Moteur de base de données
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour