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.
SQL Server Profiler Events Selection
There are a lot of options and configurations you should play around, that’s all I need for my daily works. Hope it’s useful for you.
