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:
If I want to get row count for a table how can I get it?
you can get it from here
http://shahamishm.blogspot.com/2011/02/get-row-count-for-table-sql-server-dmvs.html
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