Sql Server 2000 - How do I know which stored procedures are currently running?

Question

I would like to know which stored procedures are currently running to diagnose some performance issues. How can I find out?

Answer 1

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

Shows a procedure or trigger and current instruction.

Answer 2

You can use SQL Profiler to find out.

EDIT: If you can stop the application you are using, you can start SQL Profiler, start the application and see what works, including stored procedures.

Answer 3

I think you can run the sp_who2 command to get a list of connections, but then you will need to start tracing through SQL Profiler on a specific connection to see what it is doing. I don’t think it works with queries that already work.

Answer 4

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

Answer 5

Using Enterprise Manager, you can open the "Management Tree" section and select "Current Activity" → "Process Information". Double-clicking on the process ID will show you that the process is running. If this is a stored procedure, it will not show you the parameters. To do this, it would be better to use Brian Kim's suggestion to use SQL Profiler.