SQL server profiler to troubleshoot performance issue

SQL Server Profiler is a monitoring tool that you can use to create and manage SQL Server traces for monitoring SQL Server instance activity. These trace files can later be analyzed and replayed. Profiler has a graphical interface, which makes it easy to use

Besides auditing actions that happened on a SQL Server instance, it is useful for SQL Server performance troubleshooting, as it can track stored procedure and query execution and show the slow ones, capture statements and events that caused performance degradation, replay the specific set of events in another environment for testing purposes, etc.

To start SQL Server Profiler:

1. Open Start, All Programs (Windows + C for Windows 8)
2. Select SQL Server Profiler

Or

1. Open SQL Server Management Studio
2. From the Tools menu, select SQL Server Profiler

Or

1. Open Command Prompt
2. Type profiler
3. Press Enter

Now, you should create traces that will collect the information you need in order to monitor and troubleshoot SQL Server performance

To create a new trace:

1. In SQL Server Profiler, open File in the menu
2. Select New Trace
3. Select a SQL Server instance, Authentication Mode, and provide credentials
4. In the Event selection tab, click Show all events
5. Select the events and event columns you want to trace
   To capture information about events that occur on a database, e.g. data and log files auto-grow and auto-shrink events, select these events in Database event type

You can click on column filter and supply your filter criteria to trace specific queries.

List all columns to include all required trace parameters.