Parcourir les aide-mémoire · SQL Server

Technologie: SQL ServerCatégorie: PerformanceNiveau: IntermédiaireMis à jour

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
  1. Problème
  2. Réponse rapide
  3. Code / commandes
  4. 1. Trouver les procédures exécutées récemment
  5. 2. Trouver les procédures exécutées le plus fréquemment
  6. 3. Trouver les procédures qui consomment le plus de CPU au total
  7. 4. Trouver les procédures avec le CPU moyen le plus élevé
  8. 5. Trouver les procédures qui effectuent le plus de lectures logiques
  9. 6. Trouver les procédures avec la durée écoulée moyenne la plus élevée
  10. 7. Vérifier une procédure stockée précise
  11. 8. Trouver les procédures actuellement absentes des statistiques de procédures
  12. 9. Vérifier la durée de fonctionnement de SQL Server / la fenêtre d’observation
  13. 10. Vérifier quand les statistiques de la procédure sont entrées dans le cache
  14. 11. Utiliser Query Store pour obtenir des preuves historiques durables
  15. Interpréter le résultat
  16. Points à vérifier
  17. Nombre d’exécutions élevé + coût moyen modéré
  18. Faible nombre d’exécutions + durée moyenne très élevée
  19. Durée écoulée élevée + CPU faible
  20. CPU élevé + lectures logiques élevées
  21. cached_time est récent
  22. Aucune ligne dans sys.dm_exec_procedure_stats
  23. Notes de production
  24. Les statistiques de procédures reposent sur le cache
  25. Le cycle de vie du cache des plans compte
  26. Les moyennes peuvent masquer des écarts
  27. Supprimer une procédure exige plus que des statistiques d’utilisation
  28. Les autorisations varient selon la version
  29. Erreurs fréquentes
  30. 1. Considérer l’absence de lignes dans la DMV comme la preuve d’une non-utilisation
  31. 2. Ignorer cached_time
  32. 3. Classer uniquement par durée moyenne
  33. 4. Supprimer une procédure stockée parce qu’elle « ne s’est pas exécutée »
  34. 5. Supposer que les statistiques de procédure équivalent aux statistiques d’instruction
  35. 6. Comparer des statistiques sur différents cycles de vie du cache
  36. Étape suivante
  37. 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.

Étape suivante

Utilisez le profil d’utilisation pour choisir la prochaine investigation.

  • Nombre d’exécutions élevé + CPU total élevé → examinez les instructions coûteuses de la procédure et ses plans d’exécution.
  • Lectures logiques élevées → analysez les scans, les prédicats, les jointures et l’indexation.
  • Durée écoulée élevée + CPU faible → examinez les blocages et les types d’attente.
  • Aucune ligne de statistiques actuelle → consultez Query Store, les références dans le code source, les jobs, le code applicatif et la supervision avant de la déclarer inutilisée.
  • cached_time récent → collectez des données sur une fenêtre d’observation plus longue.
  • Procédure candidate à la suppression → vérifiez les dépendances en dehors de la DMV avant de la déprécier.
  • Procédure au comportement tantôt rapide, tantôt lent → utilisez Query Store pour une analyse historique des plans et de l’exécution.

← Tous les aide-mémoire