How to get list of stored procedures statistics

Question

How to get list of stored procedures statistics

SSCNewbee

Right there with Babe

Points: 788
More actions
July 13, 2016 at 7:04 am

#311705

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

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

SSCNewbee Right there with Babe Points: 788 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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