How many times a stored proc was run?

• skyfire1

  Valued Member

  Points: 55

  More actions

  May 23, 2007 at 8:18 pm

  #63071

  Hello all,

  I was asked a question the other day and to be honest I'm drawing a blank. The question was is there a way to find out how many times a stored proc was run in a day does sql server keep these statistics internally?

  Does anyone have any suggestions. 

  Thanks

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738673

  More actions

  May 23, 2007 at 8:58 pm

  #708511

  It doesn't in SS2000. Possibly in ss2k5, but the easy way is to build a table and add a line of code to the stored proc to log each run.

• michaela

  SSCertifiable

  Points: 5649

  More actions

  May 24, 2007 at 12:01 am

  #708536

  In sql2k5, you can see the number of times that an execution plan has been executed since it was last compiled by querying sys.dm_exec_query_stats;

  SELECT qs.execution_count,

          SUBSTRING(qt.text,qs.statement_start_offset/2,

     (case when qs.statement_end_offset = -1

     then len(convert(nvarchar(max), qt.text)) * 2

     else qs.statement_end_offset end -qs.statement_start_offset)/2)

    as query_text,

    qt.dbid, dbname=db_name(qt.dbid),

    qt.objectid

  FROM sys.dm_exec_query_stats qs

  cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

  where dbid='your db id'

  ORDER BY

          qs.execution_count DESC