[SQL Server] DMV : capture des requêtes les plus coûteuses en CPU ou en IO, et de leur plan d’exécution

Ce billet présente quelques requêtes/DMV utiles pour la capture des requêtes les plus coûteuses suivant des critères définis (temps CPU, I/O…), via notamment sys.dm_exec_query_stats.

L’intérêt de ce billet est de surtout montrer comment il est possible d’associer – grâce à sys.dm_exec_cached_plans et sys.dm_exec_query_plan – un plan d’exécution XML à une requête donnée de façon à pouvoir rapidement auditer les requêtes coûteuses.

Top 10 des requêtes les plus gourmandes par temps CPU (en millisecondes)

SELECT TOP 10 OBJECT_NAME(st.objectid,st.dbid) AS object_name
 ,st.text AS sql_text
 ,qp.query_plan AS sql_plan
 ,DB_NAME(st.dbid) AS database_name
 ,objtype AS query_type
 ,qs.creation_time AS compilation_time
 ,qs.last_execution_time AS last_execution_time
 ,qs.execution_count AS execution_count
 ,qs.total_elapsed_time/(qs.execution_count*1000) AS avg_elapsed_time
 ,(qs.total_worker_time+0.0)/(qs.execution_count*1000) AS avg_cpu_time
 ,qs.total_logical_reads/ qs.execution_count AS avg_logical_reads
 ,qs.total_logical_writes/qs.execution_count AS avg_logical_writes
 ,qs.total_physical_reads/qs.execution_count AS avg_physical_reads
 ,OBJECT_SCHEMA_NAME(st.objectid,st.dbid) AS schema_name
FROM sys.dm_exec_query_stats qs
 JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
--WHERE st.text NOT LIKE '%SELECT TOP 10%'
ORDER BY avg_cpu_time
DESC
GO

Vous pouvez filtrer la requête de façon à exclure la requête elle-même (en décommentant la clause WHERE, par exemple).

Top 10 des requêtes les plus gourmandes par temps I/O (lectures logiques)

SELECT TOP 10 OBJECT_NAME(st.objectid,st.dbid) AS object_name
 ,st.text AS sql_text
 ,qp.query_plan AS sql_plan
 ,DB_NAME(st.dbid) AS database_name
 ,objtype AS query_type
 ,qs.creation_time AS compilation_time
 ,qs.last_execution_time AS last_execution_time
 ,qs.execution_count AS execution_count
 ,qs.total_elapsed_time/(qs.execution_count*1000) AS avg_elapsed_time
 ,(qs.total_worker_time+0.0)/(qs.execution_count*1000) AS avg_cpu_time
 ,qs.total_logical_reads/ qs.execution_count AS avg_logical_reads
 ,qs.total_logical_writes/qs.execution_count AS avg_logical_writes
 ,qs.total_physical_reads/qs.execution_count AS avg_physical_reads
 ,OBJECT_SCHEMA_NAME(st.objectid,st.dbid) AS schema_name
FROM sys.dm_exec_query_stats qs
 JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
--WHERE st.text NOT LIKE '%SELECT TOP 10%'
ORDER BY avg_logical_reads
DESC
GO

Vous pouvez manipuler la requête DMV de diverses manières pour obtenir un autre classement par temps I/O en remplaçant avg_logical_reads par un autre type d’accès I/O (i.e., avg_logical_writes,…) dans la clause ORDER BY.

Pour aller plus loin

Depuis SQL Server 2008, il est possible d’utiliser une DMV plus spécifique aux procédures stockées : sys.dm_exec_procedure_stats. Pour reprendre notre exemple sur le classement des requêtes coûteuses par temps CPU, cela donnerait :

SELECT TOP 10 object_name(st.objectid,st.dbid) AS object_name
 ,st.text AS sql_text
 ,qp.query_plan AS sql_plan
 ,db_name(st.dbid) AS database_name
 ,objtype AS query_type
 ,qs.creation_time AS compilation_time
 ,ps.last_execution_time AS last_execution_time
 ,ps.execution_count AS execution_count
 ,ps.total_elapsed_time/(ps.execution_count*1000) AS avg_elapsed_time
 ,(ps.total_worker_time+0.0)/(ps.execution_count*1000) AS avg_cpu_time
 ,ps.total_logical_reads/ ps.execution_count AS avg_logical_reads
 ,ps.total_logical_writes/ps.execution_count AS avg_logical_writes
 ,ps.total_physical_reads/ps.execution_count AS avg_physical_reads
 ,object_schema_name(st.objectid,st.dbid) AS schema_name
FROM sys.dm_exec_procedure_stats ps
 JOIN sys.dm_exec_query_stats qs ON ps.plan_handle=qs.plan_handle
 JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
--WHERE st.text NOT LIKE '%SELECT TOP 10%'
ORDER BY avg_cpu_time
DESC
GO

 
Publicités

2 commentaires sur “[SQL Server] DMV : capture des requêtes les plus coûteuses en CPU ou en IO, et de leur plan d’exécution

  1. SQLhttp://sqlpro.developpez.com/pro dit :

    Tu as aussi sys.dm_exec_trigger_stats

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s