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.