Un petit billet rapide pour livrer une de mes requêtes utilisée lors des audits ou des missions d’optimisation que je réalise.

L’idée est de récupérer dans le cache d’objets la liste des procédures stockées appelées. On utilise les DMV suivantes :

• sys.dm_exec_cached_plans : pour avoir les plans d’exécution de type Proc (procédure stockées)
• sys.dm_exec_query_stats : pour avoir les statistiques d’exécution (notamment le nombre de fois où le plan a été appelé)
• sys.dm_exec_sql_text : pour avoir le code T-SQL

Les données remontées par la requête sont à relativiser. En effet, on va chercher des infos “à chaud” et donc les stats sont à pondérer. Premièrement, le nombre d’exécutions est depuis le démarrage de l’instance (ou la création de la proc). Deuxièmement, on ne voit que les procédures dont le plan est dans le cache.

Néanmoins, sur une production qui tourne depuis un moment sans downtime, les chiffres sont suffisamment stables pour être étudiés (avec discernement évidemment).

SELECT
    DB_NAME(sql.dbid), OBJECT_NAME(sql.objectid, sql.dbid), sql.text,
    MAX(qs.execution_count) as hitcounts,
    SUM(total_logical_reads / qs.execution_count) as avg_reads,
    SUM(max_logical_reads) as max_reads, SUM(min_logical_reads) as min_reads
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) sql
    INNER JOIN sys.dm_exec_cached_plans cp
        ON cp.plan_handle = qs.plan_handle
        AND cp.objtype = 'Proc'
GROUP BY DB_NAME(sql.dbid), OBJECT_NAME(sql.objectid, sql.dbid), sql.text
ORDER BY MAX(cp.usecounts) * SUM(total_logical_reads / qs.execution_count) DESC

Je vous laisse jouer avec les jointures et l’ORDER BY pour faire votre top idéal.

PS : évidemment, cette requête n’est qu’un maillon de la méthodologie que j’emploie mais comme on me la demande quelquefois en formation, je vous la donne. Cadeau .