azdzn (1/9/2012)
You can find a few information in DMVs (sys.dm_exec_query_stats) but there will not be that many details.You will only know how many times it has been called since last server restart.
You can use profiler to gather following information :
- who called the SP (hostname, loginname or ntusername depending on the authentication type)
- when it was called
- ...
sys.dm_exec_query_stats doesn't keep information since the last server restart. It keeps information on each query from the time that query enters cache until it leaves cache. When that query leaves the cache, all that data goes away. If that query goes into cache again, it starts over.
Also, Profiler is not the tool I'd recommend. Instead I'd use a server side trace, which is a scripted mechanism for gathering the data. Profiler has additional overhead when hitting the system that should be avoided.
"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