How to determine which stored procedures are being executed (MSSQL 2008 R2)

Question

I'm having a real problem with my application and SQL Server (2008 R2).

I have a bug whereby a stored procedure is failing because of a misconfigured SQLCMD variable but the call to the stored procedure is in an assembly for which I don't have the source code.

Is there a way to watch which stored procedures are being executed? Or is there a way to determine with an SQL query which stored procedures have been executed and when?

I am really stuck. Please help!

M

Start a trace in SQL Profiler, that will show all the queries being executed — radar, Oct 29 '14 at 16:19
Profiler has a high overhead, you could also do this with a server-side trace, but it's more involved. — Jaaz Cole, Oct 29 '14 at 17:01

score 0 · Answer 1 · answered Oct 29 '14 at 17:25

You could try running this against your database:

select   OBJECT_NAME([object_id], database_id), last_execution_time, execution_count
from     sys.dm_exec_procedure_stats
order by last_execution_time desc

Documentation: http://msdn.microsoft.com/en-us/library/cc280701.aspx

That gives you a snapshot at the time of execution what was last run and how many times it's been executed since it was last compiled. The table doesn't unfortunately give a log per-se of the stored procedures getting run, just when they were run last and some other helpful information.

Fore a much more involved approach, you could look at SQL Server Audit, a new feature to SQL Server 2008. I don't have much experience with it, but this should give you a starting point if you're super stuck.

How to determine which stored procedures are being executed (MSSQL 2008 R2)

1 Answers1