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
-- TOP N des procédures (et fonctions, déclencheurs) les plus coûteuses
-- 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
DECLARE @Base sysname = NULL; -- NULL = toutes les bases utilisateur
SELECT TOP (@TopN)
DB_NAME(ps.database_id) AS base,
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_nom,
OBJECT_NAME(ps.object_id, ps.database_id) AS objet,
ps.type_desc AS type_objet,
ps.execution_count AS nb_executions,
ps.total_worker_time / 1000 AS cpu_total_ms,
ps.total_worker_time / ps.execution_count / 1000.0 AS cpu_moyen_ms,
ps.total_elapsed_time / 1000 AS duree_totale_ms,
ps.total_elapsed_time / ps.execution_count / 1000.0 AS duree_moyenne_ms,
ps.max_elapsed_time / 1000.0 AS duree_max_ms,
ps.total_logical_reads AS lectures_logiques_total,
ps.total_logical_reads / ps.execution_count AS lectures_logiques_moy,
ps.total_logical_writes AS ecritures_logiques_total,
ps.cached_time AS en_cache_depuis,
ps.last_execution_time AS derniere_execution
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id > 4
AND ps.database_id < 32767 -- exclut les bases système et la base de ressources
AND (@Base IS NULL OR ps.database_id = DB_ID(@Base))
ORDER BY CASE @Tri
WHEN 'CPU' THEN ps.total_worker_time
WHEN 'LECTURES' THEN ps.total_logical_reads
WHEN 'DUREE' THEN ps.total_elapsed_time
WHEN 'EXEC' THEN ps.execution_count
END DESC;Comment lire le résultat
- Coût cumulé ou coût moyen : triez par CPU total pour trouver ce qui pèse le plus sur le serveur, puis regardez le coût moyen pour savoir s'il faut optimiser la procédure ou réduire ses appels.
- duree_max_ms comparée à la durée moyenne : un grand écart signale souvent un problème de paramètres sensibles (parameter sniffing) ou de blocages.
- en_cache_depuis : la période couverte. Une procédure recompilée récemment repart de zéro.
Précautions et suites
- La vue ne contient que les procédures présentes dans le cache de plans. Une procédure absente n'a pas forcément un coût nul : son plan peut avoir été évincé.
- Les procédures créées avec
WITH RECOMPILEn'y figurent pas, car leur plan n'est pas conservé. - Pour aller plus loin sur une procédure : plan d'exécution réel, lignes estimées et lignes réelles, puis index manquants (voir le script sur les index manquants).