Post reply

Stored procedure usage statistics

  • November 12, 2008 at 6:39 am

    #202620

    Hi All,

    Does anyone know if somewhere in Sql Server the system records the number of times, or the last usage time, or CPU time used, or some kind of statistics on the calling of stored procedures?

    We've identified a number of cursors :w00t: in stored procs to be replaced with set-based solutions - and I want to prioritise the most frequently used ones for attention.

    Thanks

  • November 12, 2008 at 6:52 am

    #897477

    You can have a look at the sys.dm_exec_query_stats DMV. That contains execution counts and CPU, read, write and duration stats.

    Unfortunately the info's only there while the plan for that proc is in cache. If the plan gets discarded from the cache (due to a recompile, a stats change, a change to an underlying object or just memory pressure) then the stats are discarded.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply