Creating Traces With SQL Server 2012

Using SQL server profiler to track database performance issues

SQL Server Profiler is a diagnostic tool included with Microsoft SQL Server 2012. It allows you to create SQL traces that track the specific actions performed against a SQL Server database. SQL traces provide valuable information for troubleshooting database issues and tuning database engine performance. For example, administrators might use a trace to identify a bottleneck in a query and develop optimizations to improve database performance.

Creating a Trace

The step-by-step process for creating an SQL server trace with SQL Server Profiler is as follows:

  1. Open SQL Server Management Studio and connect to the SQL Server instance of your choice. Provide the server name and appropriate log-in credentials, unless you are using Windows Authentication.

  2. After you open SQL Server Management Studio, select SQL Server Profiler from the Tools menu. Note that if you do not plan to use other SQL Server tools in this administrative session, you may choose to launch SQL Profiler directly, rather than go through Management Studio.

  3. Provide log-in credentials again, if you are prompted to do so.

  4. SQL Server Profiler assumes you want to start a new trace and opens a Trace Properties window. The window is blank to allow you to specify the details of the trace.

  5. Create a descriptive name for the trace and type it into the Trace Name text box.

    Select a template for the trace from the Use the Template drop-down menu. This allows you to start your trace using one of the predefined templates stored in SQL Server's library. 

  6. Choose a location to save the results of your trace. You have two options:

    • Select Save to File to save the 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. You can also set a maximum file size in MB to limit the impact the trace might have on disk use.
    • Select Save to Table to save the trace to a table within the SQL Server database. If you select this option, you are prompted to connect to the database where you want to store the trace results. You can also set a maximum trace size—in thousands of table rows—to limit the impact the trace might have on your database.
  7. Select the Events Selection tab to review the events you will monitor with your trace. Some events are automatically selected based on the template you chose. You may modify the default selections at this time and view additional options by selecting the Show All Events and ​Show All Columns checkboxes.

  8. Select Run button to begin the trace. When you are finished, select Stop Trace from the File menu.

Choosing a Template

When you begin a trace, you may choose to base it on any of the templates found in SQL Server's trace library. Three of the most commonly used trace templates are:

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

This article addresses SQL Server Profiler for SQL Server 2012. There are also earlier versions.

Format
mla apa chicago
Your Citation
Chapple, Mike. "Creating Traces With SQL Server 2012." ThoughtCo, Nov. 18, 2021, thoughtco.com/creating-traces-with-sql-server-2012-1019794. Chapple, Mike. (2021, November 18). Creating Traces With SQL Server 2012. Retrieved from https://www.thoughtco.com/creating-traces-with-sql-server-2012-1019794 Chapple, Mike. "Creating Traces With SQL Server 2012." ThoughtCo. https://www.thoughtco.com/creating-traces-with-sql-server-2012-1019794 (accessed March 19, 2024).