Parcourir les aide-mémoire · SQL Server
Trouver l’utilisation des procédures stockées
Identifiez les procédures stockées exécutées récemment, comparez leur nombre d’exécutions et leur consommation de ressources, et comprenez les limites des statistiques du cache des procédures de SQL Server.
Sur cette page
- Problème
- Réponse rapide
- Code / commandes
- 1. Trouver les procédures exécutées récemment
- 2. Trouver les procédures exécutées le plus fréquemment
- 3. Trouver les procédures qui consomment le plus de CPU au total
- 4. Trouver les procédures avec le CPU moyen le plus élevé
- 5. Trouver les procédures qui effectuent le plus de lectures logiques
- 6. Trouver les procédures avec la durée écoulée moyenne la plus élevée
- 7. Vérifier une procédure stockée précise
- 8. Trouver les procédures actuellement absentes des statistiques de procédures
- 9. Vérifier la durée de fonctionnement de SQL Server / la fenêtre d’observation
- 10. Vérifier quand les statistiques de la procédure sont entrées dans le cache
- 11. Utiliser Query Store pour obtenir des preuves historiques durables
- Interpréter le résultat
- Points à vérifier
- Nombre d’exécutions élevé + coût moyen modéré
- Faible nombre d’exécutions + durée moyenne très élevée
- Durée écoulée élevée + CPU faible
- CPU élevé + lectures logiques élevées
- cached_time est récent
- Aucune ligne dans sys.dm_exec_procedure_stats
- Notes de production
- Les statistiques de procédures reposent sur le cache
- Le cycle de vie du cache des plans compte
- Les moyennes peuvent masquer des écarts
- Supprimer une procédure exige plus que des statistiques d’utilisation
- Les autorisations varient selon la version
- Erreurs fréquentes
- 1. Considérer l’absence de lignes dans la DMV comme la preuve d’une non-utilisation
- 2. Ignorer cached_time
- 3. Classer uniquement par durée moyenne
- 4. Supprimer une procédure stockée parce qu’elle « ne s’est pas exécutée »
- 5. Supposer que les statistiques de procédure équivalent aux statistiques d’instruction
- 6. Comparer des statistiques sur différents cycles de vie du cache
- Étape suivante
- Pour aller plus loin
Problème
Vous devez savoir si une procédure stockée est encore utilisée, quelles procédures s’exécutent le plus souvent, ou lesquelles consomment le plus de CPU, de lectures ou de durée écoulée.
Les questions typiques sont les suivantes :
- Cette procédure stockée s’est-elle exécutée récemment ?
- Combien de fois s’est-elle exécutée ?
- Quelles procédures sont les plus coûteuses ?
- Quelles procédures n’apparaissent pas dans la charge de travail récente ?
- Puis-je supprimer sans risque une procédure qui semble inutilisée ?
La principale limite est que sys.dm_exec_procedure_stats est un historique fondé sur le cache, et non un historique d’utilisation permanent.
Réponse rapide
Utilisez sys.dm_exec_procedure_stats pour les procédures qui disposent actuellement de statistiques d’exécution en cache.
SELECT TOP (50)
DB_NAME(ps.database_id) AS database_name,
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.cached_time,
ps.last_execution_time,
ps.execution_count,
CAST(ps.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(
ps.total_worker_time * 1.0
/ NULLIF(ps.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
ps.total_logical_reads,
CAST(
ps.total_logical_reads * 1.0
/ NULLIF(ps.execution_count, 0)
AS decimal(18,2)
) AS avg_logical_reads,
CAST(ps.total_elapsed_time / 1000.0 AS decimal(18,2)) AS total_elapsed_ms,
CAST(
ps.total_elapsed_time * 1.0
/ NULLIF(ps.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_elapsed_ms
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
ps.last_execution_time DESC;
Utilisez cette requête pour comprendre l’activité récente présente dans le cache.
N’interprétez pas « aucune ligne renvoyée » comme la preuve que la procédure n’a jamais été utilisée.
Code / commandes
1. Trouver les procédures exécutées récemment
SELECT
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.last_execution_time,
ps.execution_count,
ps.cached_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
ps.last_execution_time DESC;
Utilisez cette requête lorsque la question est :
Quelles procédures stockées se sont exécutées le plus récemment ?
2. Trouver les procédures exécutées le plus fréquemment
SELECT TOP (50)
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.execution_count,
ps.last_execution_time,
ps.cached_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
ps.execution_count DESC;
Un nombre d’exécutions élevé peut révéler :
- des procédures d’API très sollicitées
- des modèles applicatifs trop bavards (chatty)
- des procédures utilitaires appelées fréquemment
- des procédures dont de petites inefficacités deviennent coûteuses à grande échelle
3. Trouver les procédures qui consomment le plus de CPU au total
SELECT TOP (50)
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.execution_count,
CAST(ps.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(
ps.total_worker_time * 1.0
/ NULLIF(ps.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
ps.total_worker_time DESC;
Utilisez le CPU total lorsque l’impact global sur le serveur vous importe.
Une procédure modérément coûteuse appelée des milliers de fois peut compter davantage qu’une procédure très lente appelée une seule fois.
4. Trouver les procédures avec le CPU moyen le plus élevé
SELECT TOP (50)
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.execution_count,
CAST(
ps.total_worker_time * 1.0
/ NULLIF(ps.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
CAST(ps.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
avg_cpu_ms DESC;
Le coût moyen est utile pour les procédures coûteuses à chaque appel.
Examinez toujours execution_count à côté de la moyenne.
Une moyenne fondée sur une ou deux exécutions constitue une preuve fragile.
5. Trouver les procédures qui effectuent le plus de lectures logiques
SELECT TOP (50)
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.execution_count,
ps.total_logical_reads,
CAST(
ps.total_logical_reads * 1.0
/ NULLIF(ps.execution_count, 0)
AS decimal(18,2)
) AS avg_logical_reads,
ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
ps.total_logical_reads DESC;
Utilisez cette requête pour identifier les procédures qui exercent une forte pression sur le buffer pool ou qui parcourent de grands volumes de données en cache.
Des lectures élevées ne signifient pas automatiquement « index manquant ».
Examinez les plans de requête et la logique de la procédure.
6. Trouver les procédures avec la durée écoulée moyenne la plus élevée
SELECT TOP (50)
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.execution_count,
CAST(
ps.total_elapsed_time * 1.0
/ NULLIF(ps.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_elapsed_ms,
CAST(
ps.total_worker_time * 1.0
/ NULLIF(ps.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
avg_elapsed_ms DESC;
Si la durée écoulée est élevée mais le CPU faible, examinez les attentes et les blocages.
7. Vérifier une procédure stockée précise
DECLARE @schema_name sysname = N'dbo';
DECLARE @procedure_name sysname = N'YourProcedure';
SELECT
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.cached_time,
ps.last_execution_time,
ps.execution_count,
CAST(ps.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(ps.total_elapsed_time / 1000.0 AS decimal(18,2)) AS total_elapsed_ms,
ps.total_logical_reads,
ps.total_logical_writes,
ps.total_physical_reads
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
AND ps.object_id = OBJECT_ID(
QUOTENAME(@schema_name) + N'.' + QUOTENAME(@procedure_name)
);
Si cette requête ne renvoie aucune ligne, n’en concluez pas que la procédure est inutilisée.
Elle n’a peut-être simplement pas de plan de procédure en cache actuellement.
8. Trouver les procédures actuellement absentes des statistiques de procédures
Cette requête compare les procédures utilisateur avec la DMV de statistiques fondée sur le cache.
SELECT
s.name AS schema_name,
p.name AS procedure_name,
p.create_date,
p.modify_date
FROM sys.procedures AS p
INNER JOIN sys.schemas AS s
ON s.schema_id = p.schema_id
LEFT JOIN sys.dm_exec_procedure_stats AS ps
ON ps.database_id = DB_ID()
AND ps.object_id = p.object_id
WHERE
p.is_ms_shipped = 0
AND ps.object_id IS NULL
ORDER BY
s.name,
p.name;
Interprétez correctement ce résultat :
Ces procédures n’ont actuellement aucune ligne dans
sys.dm_exec_procedure_stats.
Cela ne signifie pas :
Ces procédures n’ont jamais été exécutées.
9. Vérifier la durée de fonctionnement de SQL Server / la fenêtre d’observation
SELECT
sqlserver_start_time
FROM sys.dm_os_sys_info;
Les statistiques de procédures sont liées aux plans en cache.
Si l’instance a redémarré récemment, votre fenêtre de visibilité peut être courte.
10. Vérifier quand les statistiques de la procédure sont entrées dans le cache
SELECT
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.cached_time,
ps.last_execution_time,
ps.execution_count
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
ps.cached_time DESC;
cached_time est important, car les compteurs portent sur le cycle de vie actuel du plan et des statistiques de la procédure en cache.
11. Utiliser Query Store pour obtenir des preuves historiques durables
Si Query Store est activé, c’est généralement une meilleure source que les statistiques du cache des procédures pour l’historique d’exécution à plus long terme.
Commencez par repérer les requêtes associées au contexte d’objet de la procédure et à son historique d’exécution.
L’analyse précise dans Query Store dépend de la structure de vos procédures et requêtes, mais le principe général est le suivant :
- statistiques de procédures = vue rapide et récente du cache
- Query Store = preuves historiques persistées sur les requêtes et leur exécution
Utilisez les deux pour décider si une procédure est réellement inutilisée ou coûteuse.
Interpréter le résultat
| Champ | Pourquoi c’est important |
|---|---|
execution_count |
Nombre d’exécutions représentées par les statistiques actuellement en cache. |
cached_time |
Moment où les statistiques ou le plan de la procédure sont entrés dans le cache. Définit la fenêtre d’observation actuelle de cette ligne. |
last_execution_time |
Exécution la plus récente représentée par la ligne de statistiques actuelle. |
total_worker_time |
Temps CPU total cumulé par les exécutions de la ligne de statistiques actuelle. |
total_elapsed_time |
Durée écoulée totale cumulée. |
total_logical_reads |
Total des lectures logiques cumulées. |
total_logical_writes |
Total des écritures logiques cumulées. |
total_physical_reads |
Total des lectures physiques cumulées. |
avg_cpu_ms |
CPU par exécution, calculé. Utile uniquement avec le nombre d’exécutions et le contexte. |
avg_elapsed_ms |
Durée moyenne calculée. Peut masquer les valeurs extrêmes. |
Points à vérifier
Nombre d’exécutions élevé + coût moyen modéré
Cette procédure peut tout de même contribuer fortement à la charge de travail.
De petites inefficacités multipliées par un très grand volume d’appels peuvent consommer beaucoup de CPU et d’E/S.
Faible nombre d’exécutions + durée moyenne très élevée
Interprétations possibles :
- procédure de reporting ou de traitement batch
- charge de maintenance peu fréquente
- procédure réellement lente et problématique
- problème ponctuel lié à des valeurs de paramètres précises
Ne fixez pas les priorités sur la seule moyenne.
Durée écoulée élevée + CPU faible
Examinez :
- les blocages
- les attentes d’E/S
- la consommation réseau / des résultats
- les attentes mémoire
- la contention sur des ressources externes
CPU élevé + lectures logiques élevées
Une analyse approfondie du plan d’exécution en vaut probablement la peine.
Recherchez :
- les scans volumineux
- la stratégie de jointure
- les problèmes de cardinalité
- les instructions coûteuses répétées
- les possibilités d’indexation
cached_time est récent
Fenêtre d’observation fragile.
La procédure peut avoir des années d’utilisation en production, mais seulement quelques minutes de statistiques dans le cache actuel.
Aucune ligne dans sys.dm_exec_procedure_stats
Les raisons possibles sont notamment :
- la procédure ne s’est pas exécutée depuis les derniers événements de cache ou de réinitialisation
- le plan a été évincé
- la procédure a été recompilée
- le serveur a redémarré
- le contexte de base de données a changé
- une pression sur le cache
- des modifications de métadonnées ou de schéma
Ne la qualifiez pas d’inutilisée sur ce seul critère.
Notes de production
Les statistiques de procédures reposent sur le cache
sys.dm_exec_procedure_stats n’est pas une télémétrie historique durable.
Des lignes peuvent disparaître lorsque des plans sont retirés du cache.
La DMV est donc excellente pour :
- l’utilisation récente
- le classement récent par CPU ou par lectures
- le tri immédiat
mais peu fiable pour :
- « A-t-elle été utilisée au cours des 6 derniers mois ? »
- les décisions de dépréciation
- les rapports de conformité ou d’historique
Le cycle de vie du cache des plans compte
Les événements susceptibles de modifier la fenêtre d’observation sont notamment :
- un redémarrage
- une recompilation
- l’éviction d’un plan
- des modifications de schéma
- une pression mémoire
- des opérations de vidage du cache
Examinez toujours cached_time.
Les moyennes peuvent masquer des écarts
Une même procédure peut être :
- rapide pour 99 % des appels
- très lente pour 1 % des appels
La durée moyenne peut masquer ce comportement.
Utilisez Query Store ou une télémétrie détaillée lorsque la distribution compte.
Supprimer une procédure exige plus que des statistiques d’utilisation
Avant de supprimer une procédure, vérifiez :
- les références dans le code applicatif
- les jobs SQL Agent
- les packages ETL
- les systèmes de reporting
- les intégrations externes
- les scripts de déploiement
- la documentation
- Query Store / l’historique
- le contrôle de version
Une procédure peut être rarement utilisée tout en étant critique pour l’exploitation.
Les autorisations varient selon la version
Une visibilité étendue sur les DMV exige généralement l’autorisation appropriée sur les performances ou l’état du serveur.
Pour SQL Server 2022 et les versions ultérieures, Microsoft utilise VIEW SERVER PERFORMANCE STATE pour de nombreuses DMV de performance.
Les versions antérieures utilisent généralement VIEW SERVER STATE.
Erreurs fréquentes
1. Considérer l’absence de lignes dans la DMV comme la preuve d’une non-utilisation
C’est l’erreur la plus importante.
Aucune ligne signifie qu’il n’existe aucune ligne de statistiques actuellement en cache.
Cela ne signifie pas que la procédure n’a jamais été exécutée.
2. Ignorer cached_time
Des nombres d’exécutions sans fenêtre d’observation sont trompeurs.
10 exécutions en 5 minutes et 10 exécutions en 6 mois ont des significations très différentes.
3. Classer uniquement par durée moyenne
Les moyennes peuvent surévaluer des procédures ponctuelles et sous-évaluer une charge de travail exécutée fréquemment.
Examinez :
- la consommation totale de ressources
- la consommation moyenne de ressources
- le nombre d’exécutions
- l’importance métier
4. Supprimer une procédure stockée parce qu’elle « ne s’est pas exécutée »
Avant la suppression, vérifiez les références en dehors des statistiques d’exécution de SQL Server.
Des procédures d’exploitation rarement utilisées peuvent être critiques lors d’incidents, de clôtures mensuelles ou annuelles, ou de procédures de reprise.
5. Supposer que les statistiques de procédure équivalent aux statistiques d’instruction
Une procédure stockée peut contenir plusieurs instructions au comportement très différent.
Utilisez Query Store, les plans d’exécution ou les DMV au niveau des instructions lorsque vous devez savoir quelle instruction est coûteuse.
6. Comparer des statistiques sur différents cycles de vie du cache
Si une procédure a été recompilée ou évincée, les compteurs peuvent repartir de zéro.
Ne comparez pas les compteurs actuels à des instantanés plus anciens sans tenir compte des réinitialisations.
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Restez au courant
Recevez uniquement les nouvelles de Javi Builds qui vous intéressent.
Consultez votre boîte de réception
Je vous ai envoyé un lien de confirmation. Votre abonnement sera actif après confirmation.