Les requêtes de cette page lisent des informations de l'instance sans modifier vos données. Les commandes qui modifient quelque chose sont signalées dans le texte. Testez toujours sur un environnement non critique avant la production, et relisez chaque requête avant de l'exécuter.
Le script
Les deux requêtes lisent le cache de plans : elles ne voient donc que ce qui s'y trouve encore. Un redémarrage du service ou un vidage du cache remet tout à zéro.
-- TOP N des requêtes ad hoc les plus coûteuses (depuis la mise en cache des plans)
-- Droit requis : VIEW SERVER STATE. Versions : SQL Server 2008 et suivantes.
SET NOCOUNT ON;
DECLARE @TopN int = 20;
DECLARE @Tri varchar(10) = 'CPU'; -- CPU | LECTURES | DUREE | EXEC
-- A. Plan par plan : chaque entrée du cache est une ligne
SELECT TOP (@TopN)
DB_NAME(CONVERT(int, pa.value)) AS base,
qs.execution_count AS nb_executions,
qs.total_worker_time / 1000 AS cpu_total_ms,
qs.total_worker_time / qs.execution_count / 1000.0 AS cpu_moyen_ms,
qs.total_elapsed_time / 1000 AS duree_totale_ms,
qs.total_logical_reads AS lectures_logiques_total,
qs.total_logical_reads / qs.execution_count AS lectures_logiques_moy,
qs.total_logical_writes AS ecritures_logiques_total,
qs.creation_time AS plan_cree_le,
qs.last_execution_time AS derniere_execution,
SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset) / 2 + 1) AS requete
FROM sys.dm_exec_query_stats AS qs
JOIN sys.dm_exec_cached_plans AS cp
ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
OUTER APPLY (SELECT value
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = 'dbid') AS pa
WHERE cp.objtype = 'Adhoc'
ORDER BY CASE @Tri
WHEN 'CPU' THEN qs.total_worker_time
WHEN 'LECTURES' THEN qs.total_logical_reads
WHEN 'DUREE' THEN qs.total_elapsed_time
WHEN 'EXEC' THEN qs.execution_count
END DESC;
-- B. Regroupé par empreinte : une même requête avec des valeurs différentes
-- produit autant de plans. query_hash les rassemble.
SELECT TOP (@TopN)
qs.query_hash,
COUNT(DISTINCT qs.plan_handle) AS nb_plans,
SUM(qs.execution_count) AS nb_executions,
SUM(qs.total_worker_time) / 1000 AS cpu_total_ms,
SUM(qs.total_elapsed_time) / 1000 AS duree_totale_ms,
SUM(qs.total_logical_reads) AS lectures_logiques_total,
MAX(qs.last_execution_time) AS derniere_execution,
MAX(SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset) / 2 + 1)) AS exemple_requete
FROM sys.dm_exec_query_stats AS qs
JOIN sys.dm_exec_cached_plans AS cp
ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE cp.objtype = 'Adhoc'
GROUP BY qs.query_hash
ORDER BY CASE @Tri
WHEN 'CPU' THEN SUM(qs.total_worker_time)
WHEN 'LECTURES' THEN SUM(qs.total_logical_reads)
WHEN 'DUREE' THEN SUM(qs.total_elapsed_time)
WHEN 'EXEC' THEN SUM(qs.execution_count)
END DESC;Comment lire le résultat
- cpu_total_ms : le coût cumulé. Une requête rapide exécutée des millions de fois peut coûter plus qu'une requête lente rare.
- cpu_moyen_ms et lectures_logiques_moy : le coût d'une exécution. Ils orientent vers la requête à réécrire ou l'index à créer.
- nb_plans (requête B) : un nombre élevé révèle des requêtes non paramétrées, qui encombrent le cache de plans.
- plan_cree_le : date de mise en cache. Les chiffres ne couvrent que la période depuis cette date.
Précautions et suites
- Le cache est volatil : relancez le script aux heures de forte charge, et non juste après un redémarrage.
- Beaucoup de plans ad hoc pour la même empreinte : envisagez le paramétrage côté application, ou l'option de base de données
OPTIMIZE FOR AD HOC WORKLOADS(paramètre d'instance) pour limiter la mémoire consommée. - Pour les requêtes planifiées dans des procédures, voir le script sur les procédures les plus coûteuses.
- Sur SQL Server 2016 et suivants, Query Store donne un historique durable, qui survit aux redémarrages.