Fundamentals of SQL Trace in SQL Server 2012

Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 6036 red flag
Rating: 4 out of 5  
 1 vote(s)

An SQL Server trace can be used to record events that you are interested in. You can create trace file and trace table workloads easily using SQL Server Profiler.

In order to keep from facing performance bottlenecks, SQL Server DBAs require constant monitoring and tuning of database performance. SQL Server has built-in tools to aid administrators in achieving the desired performance goals. Each version of SQL Server offers database performance monitoring tools. These tools need to run specific workloads against the database to evaluate and monitor its performance. These workloads can be Transact-SQL scripts, trace files, or trace tables. An SQL Server trace can be used to record events that you are interested in. You can create trace file and trace table workloads easily using SQL Server Profiler. Moreover, you can use Transact-SQL system stored procedures to create traces. You can invoke these stored procedures from within the application and create traces manually.

You can add specific event classes to your trace definition to include all events of your choice in the trace. You can choose to save the trace information to a file or SQL Server Management Objects (SMO), from where applications managing SQL Server can access this information.

SQL Trace Architecture

Several event classes are available for managing events that occur in an instance of SQL Server Database Engine. These event classes are used in a trace definition to capture information about related events. You can view these event classes in SQL Server Profiler by navigating to the Events Selection tab of the Trace File Properties dialog box. Any source that generates a trace event is known as an Event Source. An event source can be a lock or object in SQL Server. Whenever an event is created for which the corresponding event class is included in the trace definition, the information about that event is captured by the trace. If you have set filters for this event class, the information is sent to a queue after applying the filters. The queued information is either recorded in a file or used by SMO for applications that manage SQL Server.

Some key terms that are used to describe tracing are listed below:

Events, Event Classes, and Data Columns

Events occur whenever an action is performed in an instance of SQL Server Database Engine. An event class refers to specific type of event that can be recorded by a trace. Event classes contain data columns that represent characteristics of an event.

Trace, Trace Definition, Trace File, and Trace Table

A trace comprises a set of events and all data returned by the Database Engine. A trace definition controls the type of information that is gathered by the trace. It includes data columns of event classes and filters to identify the events to be recorded during tracing. All information gathered by a trace can be saved to a file known as trace file. You can also save this information in a table in SQL Server Profiler known as trace table.

Creating Traces Using System Stored Procedures

Transact-SQL system stored procedures provide you the ability to create traces manually and develop custom applications that meet the requirements of your business. You can follow these steps to perform tracing using system stored procedures:

  • Use 'sp_trace_create' to define a new trace. The trace created will be automatically set to the stopped state.

  • Use 'sp_trace_setevent' to add an event class or data column to a newly created trace.

  • Use 'sp_trace_setfilter' to apply filters to the trace for limiting the events to be recorded in the trace.

  • Use 'sp_trace_setstatus' for starting the trace.

  • Use 'sp_trace_setstatus' for stopping the trace.

  • Use 'sp_trace_setstatus' for closing the trace.

Saving Trace Results to a File

The information about events collected in a trace can be saved to a file. This trace file may reside in your local directory or a network directory and is saved with an extension '.trc'.

There are various benefits of using trace files as shown below:

  • Trace files are used as workloads during performance analysis or Database Engine Tuning Advisor analysis.

  • Trace files simplify addressing a problem by allowing you to correlate trace events.

  • You can replay traces easily using trace files.

  • Trace files also aid in performing query optimization.

To create a trace file and move all trace results to this file, you can use the '@tracefile' argument of the stored procedure 'sp_trace_create'. So, these settings can be done while creating traces. However, you need to ensure that the destination directory should be accessible to the server.

In case you are using SQL Profiler, you can save these results either to a file or table. If you save the trace results to a table, you can fetch the desired events from this table.

Improving Access to Trace Data

The space in the temp directory is critical to improving access to the trace data. SQL Server Profiler uses this free space to achieve faster data access. If the available space in the temp directory is less than 10 MB, all operations being performed by SQL Server Profiler will come to a halt.

Another concern is the growing size of temp directory. To prevent the temp directory from getting oversized, you need to configure the TEMP environment variable to change the location of the temp directory. You should place it on a drive other than your system drive.

The method to change the value for the TEMP environment variable is similar in almost every Windows operating system.

Steps to change the TEMP environment variable in Windows:

  • Go to 'Start', select 'Control Panel', and click 'System'. The 'System Properties' dialog box is displayed on your screen.

  • In the displayed dialog box, select the 'Advanced' tab and click 'Environment Variables'.

  • You will see a list of System Variables. Browse this list and select the row for TEMP variable. Next, click 'Edit'. The 'Edit System Variable' dialog box will show up.

  • In the displayed dialog box, specify the path and name of the directory where you want to place the temp directory.

  • Click 'OK' to save these settings.

SQL Server also has a default trace that is created automatically and comprises five trace files stored in the SQL Server installation directory. With time, these trace files are rolled over.

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Adam Gorge
Member Level: Starter
Member Status: Member
Member Since: 3/20/2013 11:56:27 PM
Country: United States
Adam Gorge is an expert handler of SQL Database errors, SQL database corruption issues etc. He has written many articles on SQL database, its inbuilt utilities, SQL database free tools & sql database recovery software etc.

Login to vote for this post.

Comments or Responses

Posted by: Vasanthmvp on: 5/16/2013 | Points: 25
Hi Adam,
Nice article. Your style of enunciation is good.

Posted by: Adamgorge on: 5/16/2013 | Points: 25
Thanks Vasanth..

Login to post response

Comment using Facebook(Author doesn't get notification)