Sql Server 2000 - How can I find out what stored procedures are running currently?

Question

I'd like to know what stored procedures are currently running to diagnose some performance problems. How can I find that out?

Answer 1

You can use SQL Profiler to find that out.

EDIT: If you can stop the app you are running, you can start SQL Profiler, run the app and look at what's running including stored procedures.

Answer 2

I think you can do execute sp_who2 to get the list of connections, but then you'll need to run a trace through SQL Profiler on the specific connection to see what it's executing. I don't think that works with queries that are already running though.

Answer 3

Using Enterprise Manager, you can open the Management tree section, and choose Current Activity -> Process Info. Double clicking on a Process ID will show you what that process is running. If it's a stored procedure, it will not show you the parameters. For that it would be better to use Brian Kim's suggestion of using the SQL Profiler.

Answer 4

DBCC INPUTBUFFER will show you the first 255 characters of input on a spid (you can use sp_who2 to determine the spids you're interested in). To see the whole command, you can use ::fn_get_sql().

Answer 5

Very useful script for analyzing locks and deadlocks: http://www.sommarskog.se/sqlutil/aba_lockinfo.html

It shows procedure or trigger and current statement.