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.

 


SELECT
TOP 100 qt.text
AS
‘SP Name’, qs.execution_count AS ‘Execution Count’,

qs.execution_count/DATEDIFF(Second, qs.creation_time,
GetDate())
AS
‘Calls/Second’,

qs.total_worker_time/qs.execution_count AS
‘AvgWorkerTime’,

qs.total_worker_time AS
‘TotalWorkerTime’,

qs.total_elapsed_time/qs.execution_count AS
‘AvgElapsedTime’,

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


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


FROM
sys.dm_exec_query_stats
AS qs


CROSS
APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt


WHERE qt.dbid =
db_id()
— Filter by current database


ORDER
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.

Comments
  1. Alex says:

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

  2. Chris Shaw says:

    Alex,

    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.
    Chris

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