How To Check Which Stored Procedures Or SQL Commands Are Running On SQL Server

Posted by Hoan Huynh in SQL Server

May 5, 2011

4 Comments

In Microsoft SQL Server, there is a tool called SQL Server Profiler which allow to capture and see which Stored procedures or SQL statements are called or submitted to your SQL Server. It’s very useful while you’re developing your application in debugging mode on your staging environment or to see which Commands are affecting your server’s performance with slow queries or executions on production server.

Using SQL Server Profiler, you can do the following:

Create a trace that is based on a reusable template
Watch the trace results as the trace runs
Store the trace results in a table
Start, stop, pause, and modify the trace results as necessary
Replay the trace results

To run the SQL Server Profiler, you can follow either of methods below:

1. From Start Menu

Click Start menu => go to All Programs => Microsoft SQL Server 2008 R2 (it may be Microsoft SQL Server 2008 on your box) => Performance Tools => SQL Server Profiler

Start SQL Server Profiler From Start Menu

2. From Microsoft SQL Server Management Studio

Open the SQL Server Management Studio from your shortcut or Start menu then go to Tools and click on SQL Server Profiler.

Start SQL Server Profiler From Management Studio

Start tracing Stored Procedures and SQL commands

After start the SQL Server Profiler, click on the New Trace icon on the top-left menu or go to File menu then click on New Trace … sub menu. It will display a Connect to Server box, simply enter login detail to a SQL Server instance you want to connect and click Connect button.

New Trace and Login to SQL Server Profiler

After logged in to your SQL Server, it will display the Trace Properties box. On General tab, you can specify some information such as Name, Save to file or Time to stop your tracing,etc. Let’s ignore this step, I usually focus on Events Selection tab. As we want to trace and check which stored procedures and sql commands are running behind our application so, let un-check the options Audit Login, Audit Logout and ExistingConnections then click Run button to see what’s running behind the sense.