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:

Anonymous said...

If I want to get row count for a table how can I get it?

Amish Manubhai Shah said...

you can get it from here

http://shahamishm.blogspot.com/2011/02/get-row-count-for-table-sql-server-dmvs.html

Jay said...

We are trying to figure out why we have a procedure, same object_id, same database_id that is listed twice in this view.

We also notice that when we see this the procedure runs incredibly slow, causing timeouts.

Procedure normally runs sub-second, and when we see this situation the procedure may run for minutes.

Only way we have resolved this is to drop and create the procedure.

Anyone have this issue?

Post a Comment

Amazon Contextual Product Ads

Popular Posts