How To Create a Trace with SQL Server Profiler

Traces allow you to track the specific actions performed against a SQL Server databases. They provide valuable information for troubleshooting database issues and tuning database engine performance. In this tutorial, we walk through the process of creating a SQL Server Trace with SQL Server Profiler, step-by-step. Note: This article is for users of SQL Server 2008 and earlier. If you are using SQL Server 2012, read Creating Traces with SQL Server 2012.

Difficulty: Average

Time Required: 15 minutes

Here's How:

Open SQL Server Management Studio by selecting it from the Start menu.
From the Tools menu, choose SQL Server Profiler.
When SQL Server Profiler opens, select New Trace from the File menu.
SQL Server Profiler will then prompt you to connect to the SQL Server instance you wish to profile. Provide the connection details and click the Connect button to continue.
Create a descriptive name for your trace and type it into the "Trace Name" textbox.
Select a template for your trace from the drop-down menu. (See the Tips below for information on some commonly used trace templates)
Select Save to File to save your trace to a file on the local hard drive. Provide a file name and location in the Save As window that pops up as a result of clicking the checkbox.
Click on the Events Selection tab to review the events you may monitor with your trace. Some events will automatically be selected based upon the template you chose. You may modify those default selections at this time. You may view additional options by clicking the Show All Events and Show All Columns checkboxes.

Click the Run button to begin your trace. SQL Server will begin creating the trace, providing details as shown in the image. (You may click on the image to enlarge it.) When you are finished, select Stop Trace from the File menu.

Tips:

The Standard template collects a variety of information about SQL Server connections, stored procedures and Transact-SQL statements.
The Tuning template collects information that may be used with the Database Engine Tuning Advisor to tune your SQL Server's performance.
The TSQL_Replay template gathers enough information about each Transact-SQL statement to recreate the activity in the future.

What You Need: