I am addressing some performance issues with our SQL Server 2000 setup and suspect that concurrently running stored procedures might be responsible for the slowdown. To get to the bottom of the problem, I need a reliable method to list all stored procedures that are executing at any given time. Could someone offer guidance on using specific SQL queries or built-in tools to achieve this? Detailed instructions or examples of how to monitor these active procedures would be greatly appreciated, as it will help diagnose and resolve the performance bottlenecks.
A practical approach involves setting up a SQL Profiler trace to capture stored procedure activity. In SQL Server 2000, this enables monitoring of SP:Starting and SP:Completed events, which gives you a real-time view of procedures being executed. Configuring the trace to focus on specific databases or filtering events based on duration can help isolate potential performance issues. I have used this method in production environments to pinpoint problematic stored procedures, and it has proven more insightful than relying solely on system tables for rapid diagnostics.
i used sp_who2 and cross-queried the sysprocesses table to check runnin sp’s. its not perfect but it helps spot which stored prc’s are actve in sql2000. hope it helps!