August 13, 2012 at 3:28 am
How to find the latest used stored procedure.
August 13, 2012 at 4:00 am
You mean the last stored procedure that's been run in a DB? Unless you have some auditing or tracing running, there's no reliable way to determine that.
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
August 13, 2012 at 4:36 am
It's a less than perfect approach, but you could simply query sys.dm_exec_query_stats (or if you really are only interested in procedures, then sys.dm_exec_procedure_stats) and order by the last execution date descending. You won't be able to see who called it, what parameters were used or anything really useful like that, but you will be able to see the most recent query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2012 at 4:52 am
Assuming of course that the procedure's plan was ever cached, has not been aged out of cache and the SQL instance hasn't been restarted since
The plan cache will give you a rough idea that is not certain to be correct.
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
August 13, 2012 at 5:14 am
GilaMonster (8/13/2012)
Assuming of course that the procedure's plan was ever cached, has not been aged out of cache and the SQL instance hasn't been restarted sinceThe plan cache will give you a rough idea that is not certain to be correct.
Yep. All good points. A far less than perfect approach.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2012 at 5:30 am
Thanks Gila And Grant. i was able to find the answer through sys.dm_exec_procedure_stats but when i tried for sys.dm_exec_query_stats , over here how can we specify whether we are searching for stored procedure or view or etc.
August 13, 2012 at 5:45 am
That's what sys.dm_exec_procedure_stats is for. It's a sub-set of query stats that just shows procedures.
Views don't have plans, so you'll never see a view alone in any of the query stats DMVs
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply