sys.dm_exec_procedure_stats DMV to get cached proc stats


We all are used to sys.dm_exec_query_stats which gives us useful information for queries running on the server.
Similarly in SQL 2008 we can get details for procedures. For this we will have to use sys.dm_exec_procedure_stats
It is supported in SQL 2008 and higher versions.



Its provides aggregated results for cached procedures since last the procedure was cached.If procedure is recompiled every time then its result will not have aggregated data.

Lets have some look into it.

SELECT TOP 10 *
FROM   sys.dm_exec_procedure_stats




SELECT Db_name(database_id)                   databaes_name,
       p.name                                 proc_name,
       p.type                                 proc_type,
       execution_count,
       total_physical_reads,
       total_physical_reads / execution_count avg_physical_reads,
       total_logical_reads,
       total_logical_reads / execution_count  avg_logical_reads,
       total_elapsed_time,
       total_elapsed_time / execution_count   avg_elapsed_time
FROM   sys.procedures p
       JOIN sys.dm_exec_procedure_stats d
           ON p.object_id = d.object_id
WHERE  d.database_id = Db_id()


3 comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts