1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Discuss in my forum

Creating Traces with SQL Server 2012

Using SQL Profiler to Track Database Performance Issues

By

Creating Traces with SQL Server 2012
SQL 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 databases. 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 that might improve database performance.

Creating a Trace

In this tutorial, we walk through the process of creating a SQL Server Trace with SQL Server Profiler, step-by-step. Here's how you can create your own SQL Server traces. Note that these instructions are for SQL Server 2012. Users of earlier versions of SQL Server should instead read Creating Traces with SQL Profiler.
  1. Open SQL Server Management Studio and connect to the SQL Server instance of your choice. You will need to provide the server name and appropriate logon credentials (unless you are using Windows Authentication).
  2. Once you've opened SQL Server Management Studio, choose 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 login credentials again, if you are prompted.
  4. SQL Server Profiler will then assume you wish to start a new trace and open a Trace Properties window, similar to that shown above. The window will be blank and allow you to specify the details of your trace.
  5. Create a descriptive name for your trace and type it into the "Trace Name" text box.
  6. Select a template for your trace from the "Use the Template" drop-down menu. This allows you to start your trace using one of the predefined templates stored SQL Server's library. See the section "Choosing a Template" below for descriptions of three of the most commonly used SQL Server trace templates.
  7. Choose a location to save the results of your trace. You have two options here:
    • 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. You may also set a maximum file size (in MB) to limit the impact your trace might have on disk use.
    • Select Save to Table to save your trace to a table within your SQL Server database. If you select this option, you will be prompted to connect to the database where you wish to store your trace results. You may also set a maximum trace size (in thousands of table rows) to limit the impact your trace might have on your database.
  8. 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.
  9. Click the Run button to begin your trace. SQL Server will begin creating the trace. When you are finished, select Stop Trace from the File menu.

Choosing a Template

When you begin your 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 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.
  1. About.com
  2. Technology
  3. Databases
  4. SQL Server
  5. Creating Traces with SQL Server 2012 and SQL Profiler

©2014 About.com. All rights reserved.