Cette requête permet de déterminer les procédures stockées qui ont fait le plus d’accès disque (I/O).
SELECT
DB_NAME(st.dbid) DatabaseName
,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
,OBJECT_NAME(objectid,st.dbid) ProcedureStockee
,max(cp.usecounts) Nb_Execution
,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) total_IO
,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) / (max(cp.usecounts)) Moyenne_total_IO
,sum(qs.total_physical_reads) total_physical_read
,sum(qs.total_physical_reads) / (max(cp.usecounts) * 1.0) Moyenne_physical_read
,sum(qs.total_logical_reads) total_logical_reads
,sum(qs.total_logical_reads) / (max(cp.usecounts) * 1.0) Moyenne_logical_read
,sum(qs.total_logical_writes) total_logical_writes
,sum(qs.total_logical_writes) / (max(cp.usecounts) * 1.0) Moyenne_logical_write
FROM
sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
JOIN sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
WHERE
DB_NAME(st.dbid) is not null
AND cp.objtype = 'proc'
GROUP BY
DB_NAME(st.dbid)
,OBJECT_SCHEMA_NAME(objectid,st.dbid)
,OBJECT_NAME(objectid,st.dbid)
ORDER BY
sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) desc
Pas de commentaire reçu(s)
Laisser une réponse