What Procs are being used?

Posted: June 29, 2010 in DMV Collection, SQLServerPedia Syndication

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.execution_count/DATEDIFF(Second, qs.creation_time,

qs.total_worker_time/qs.execution_count AS

qs.total_worker_time AS

qs.total_elapsed_time/qs.execution_count AS

qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,

DATEDIFF(Minute, qs.creation_time,
‘Age in Cache’

AS qs

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.

  1. Alex says:

    Once i have this data, what can i do with it?

  2. Chris Shaw says:


    There is a lot to be said just knowing what has the most activity in your database, beyond that you can make sure they are the fastest running procs on your system, you can also use that information as a baseline to help you in the future if you suspect something starts to slow down.

    Hope that helps.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s