Have you ever pondered about what the most popular procedures in your database are? Maybe you are trying to see if you can performance tune your database by researching what procedures are called the most often and perform the worst. Either way knowing the pattern of your databases can really only help you in the long run…
A DMV that I received from Glenn Berry will give you the information you seek.
TOP 100 qt.text
‘SP Name’, qs.execution_count AS ‘Execution Count’,
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,
‘Age in Cache’
sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid =
— Filter by current database
BY qs.execution_count DESC
One of the really nice numbers I like with the use of this DMV is the use of the Calls per second. This puts a very real number on the stats that you are looking at.