How to get list of stored procedures statistics

  • Hi,

    I'm trying a way to find the list of stored procedures statistics. Am looking for

    Name of SP

    User who executed it

    Did user did a DROP or CREATE

    When was it last run

    What processes are using that particular SP

    Thanks

  • 1) SELECT name from sys.procedures

    2) SQL does not keep history of this information. You'll need to set up an extended events session to track this going forward

    3) Short term history will be in the default trace, may be anything from a few hours to weeks worth. If you want to track over longer time, you'll need to set up an extended events session

    4) You might be able to get some info from sys.dm_exec_procedure_stats, but that's based on plans in cache, if the procedure's execution plan isn't in cache, the data won't be there. If you want to track this, you'll need to set up an extended events session

    5) As in what current connections are running it?

    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
  • GilaMonster (7/13/2016)


    1) SELECT name from sys.procedures

    2) SQL does not keep history of this information. You'll need to set up an extended events session to track this going forward

    3) Short term history will be in the default trace, may be anything from a few hours to weeks worth. If you want to track over longer time, you'll need to set up an extended events session

    4) You might be able to get some info from sys.dm_exec_procedure_stats, but that's based on plans in cache, if the procedure's execution plan isn't in cache, the data won't be there. If you want to track this, you'll need to set up an extended events session

    5) As in what current connections are running it?

    Thanks Gail Shaw,

    I want all this information from recent dates, at max a week. For the@5 yes i need for the current connections.

  • See my above reply for what you need to do in order to be able to get the information. For 5, I think you're going to need an extended event session as well.

    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 4 posts - 1 through 3 (of 3 total)

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