SQL Server Reference Guide

Once you've learned the basic concepts in SQL Server, you've ensured that your servers are configured properly, your maintenance plans are set up, and you've tuned the system as far as you can, then it's time to delve a little deeper.

Some of the features in SQL Server aren't always well understood by DBA's, partly because of other time pressures and sometimes because the feature seems a bit too difficult to implement quickly. One of those features is SQL Trace – a tool you should learn and use.

The official documentation for SQL Trace from Microsoft contains everything you need to know to work with this feature, but it can be a bit difficult to find a simple example of the complete process. I thought I would create one here that you can read in a single tutorial.

SQL Trace is very similar to SQL Server Profiler, a tool I've documented here. Basically each tool collects events that happen on the server. For this exercise, we'll only capture one activity – the "SQL Statement" event, which records SQL Statements. Within that event, we'll capture specific items, in particular the identification of the account that runs the statement (the SPID), the statement itself, and the duration of the statement. We'll save that information into a trace file, which can be read and played back by SQL Profiler, sent to a table, or even read with a function within Transact-SQL. You can also use a trace file as input for tuning your system.

The feature works through the use of several stored procedures. While that doesn't seem too difficult, it's the number and type of parameters each one takes that can be a little confusing. It's actually much easier to use SQL Profiler since the graphical interface makes choosing the options simple. The advantage of using SQL Trace, however, is that it can be started and stopped using code, a job or another program. Using scripting options you can start the trace automatically on a condition, or using other tools you can start the trace using a schedule. You just have more options if you use SQL Trace to track activity.

The four stored procedures you need to work with are as follows:

• sp_trace_create – Creates a trace
• sp_trace_setevent – Sets the events and objects capture
• sp_trace_setfilter – Includes and excludes data
• sp_trace_setstatus - Starts, stops and closes a trace

That's really all there is to it – the hard part is putting those stored procedures to work, and then putting all of the variables in place. We'll take it step by step here, and when we're done you'll be able to use this process over and over.

To begin, we need to find a database to work with. You can use any database you would like to monitor, but you will need its database ID number. You can find that with this query:

/* Find a database to work with */
SELECT name
, database_id
FROM sys.databases
ORDER BY name;
GO

You can add a WHERE clause if you already know the name of the database you want. In my case, I'll be working with the AdventureWorks sample database on a SQL Server 2005 Express instance. The number for that database on my system is "5". You can replace that with the number of the database you want to monitor.

Once you get past this step, you'll need to run the script in two parts. The first sets up and runs the trace, and the second closes it out and reads it. I'll show you both halves at the end of this tutorial so don't try and run the specific pieces as I show them to you. The reason is that the variables you set and collect during the process will go "out of scope" if you run them in pieces, so the next part of the script won't know what you're talking about from the previous – each section is treated as a "batch" of commands that it thinks you want to begin and end with each run. That's not what we're looking for.

So I'll break down the script into steps to explain it, but then I'll show you a complete two-part example at the end.

Step One – Create the Trace Variables

No, you really don't have to do this, but some of the variables for the stored procedures require specific data types, like a bit or an integer. By setting up a few variables first, you don't have to worry about using the CAST or CONVERT functions to set up the numbers you need. Let's do that now, and then I'll explain what they are when we start using them:

/* Set Up the Variables Needed for the trace */
DECLARE @traceHandle INT; 
DECLARE @traceID INT;
DECLARE @maxFileSize bigint; 
DECLARE @traceFile NVARCHAR(128); 
DECLARE @onBit bit;

Step Two – Create the Trace

In this step we're going to use those variables and begin to create the trace definition:

/* Create a trace */
EXEC @traceHandle = sp_trace_create @traceID OUTPUT 
, 0
, @traceFile
, @maxFileSize
, NULL;

First, we're using the variable @traceHandle to hold the results for the trace. You can call this variable anything you want, as long as it is an integer. These steps just give SQL Server a number to track the process of the trace with, since it is first created (instantiated) and then destroyed when we are done with it. It's a kind of object.

Next, we set that variable to the results of the first stored procedure – sp_trace_create – and start passing parameters to it.

The first parameter is a number we can use later to identify this particular trace run, called @traceID. We'll need that number later, because the basic process is to start the trace, and then allow it to gather the activity data. We'll then stop the trace and close it using that number.

The OUTPUT keyword is required, and just means we're extracting data from the trace.

The next value of 0 indicates that we don't have any options for this trace. You can see Books Online (the reference at the end of this article) for what those do.

The @traceFile variable sets where the trace file will go on the hard drive. Don't specify an extension here; it will be automatically set to .trc. And make sure that the SQL Server service can access the path you send the file to, or the whole thing will just fail.

The @maxFileSize variable sets the maximum trace file size to 5MB. This is actually the default, but I like to specify it anyway. Using a variable now lets you adjust it in the future.

The final NULL value there sets the fact that we don't want a stop-time – we just want it to keep running until we stop it manually. This can be a useful feature, but you still have to close out the file and read it so I use the second half of the script at the end of the tutorial to stop, close and read the file.

Step three – Create the Trace Events

The trace doesn't do anything yet. You've just set up a trace and told it where to go. Now you need to track some classes and events to watch.

In the example below, I want to watch the SQL Statements that are running against the AdventureWorks database:

/* Set the events and data to collect */ 
EXEC sp_trace_setevent @traceID
, 40
, 1
, @onBit;
EXEC sp_trace_setevent @traceID
, 40
, 12
, @onBit; 
EXEC sp_trace_setevent @traceID
, 40
, 13
, @onBit;

This part needs a bit of explaining, since it is the heart of what you want to do. We're using the sp_trace_event stored procedures to feed the trace (using @traceID) the events we care about. The first number, 40, is the "Statement Starting" event.

The second number in that first statement group (number 1) is the column within the event that I want to track. In this case, it's the text data for the statement. It will show me the full SQL Server statement the user sent.

But that isn't all I want to know about the SQL Statements. I add the same event, but this time I use another column (number 12) that shows me the user's ID (the SPID) that ran the query. And the next statement includes number 13, which shows me the duration of the statement in milliseconds.

You can find a full list of these events and their columns here.

The @onBit variable just turns the trace event on.

Step Four – Filter the Trace Data

The trace sends back an amazing array of data about the engine. Everything, in fact. There are some things we don't want to see, such as the data from the trace process itself or other master database operations. This part of the script only gets the data from the right database:

/* Set a filter for the database to only include databaseId = 5 */ 
EXEC sp_trace_setfilter @traceID
, 3
, 1
, 0
, 5;

We're using the sp_trace_setfilter stored procedure using the @traceID variable again to turn off some data. The first number, 3, uses the same columns from the previous reference. In this case, number 3 is the database ID.

The next value is the operator we want against the database ID. In this example the number is 0, which sets the database to use an AND or an OR operation. 0 is AND, 1 is OR.

The next value, 1, is the "does not equal" operator (you can find a list of those operators here). So far we have "Database ID does not equal..."

Finally we use the database number we found at the top of this example. In my case, that's 5, for AdventureWorks. So we're blocking out every database with the exception of 5.

Step Five – Filter the Trace Data

Now we just need to start the trace and get its number, since once we run this part of the script that variable will disappear. Let's run it and check the number:

/* Start the trace */
EXEC sp_trace_setstatus @traceID, 1;
/* Show the trace id */
SELECT @traceID;

That came back as "2" for me.

Step Six – Stop and Close the Trace

The users are now allowed back into the database and they work merrily away. Before the trace file gets to 5 MB (our max file size), we stop the trace, and then close it. In my case, the trace number from the last setup was "2":

/* Work is done in another query */
/* Stop the trace */ 
EXEC sp_trace_setstatus 2, 0
/* Close the trace */ 
EXEC sp_trace_setstatus 2, 2

The sp_trace_setstatus stored procedure; the one we used to turn on the script with a value of 1 is now used with a value of 0 to cut trace number 2 off. As you can see, I can't use the @traceID variable anymore because I completed the run of the previous steps and now it's gone.

You can do some other work and then turn the trace back on, using that same stored procedure. That way you can "tune" things to circle in on a certain behavior by turning the trace on and off. In our case we'll use the status of "2" on trace number 2 to close out the last of the file and make it available for use.

Only one more step.

Step Seven – Read and Store the Trace

We now have the trace file, and many programs can read it, such as SQL Server Profiler. We can also read it in using a special function called fn_trace_gettable. It takes the name of the file as a parameter, and you can format it in various ways. I'll use the default format, and read it into a new table called TraceTable with the SELECT...INTO command.

 /* Load into a new table */ 
SELECT * 
INTO TraceTable 
FROM ::fn_trace_gettable(’C:\temp\TestTrace.trc’, DEFAULT);
/* Get the trace */
SELECT * 
FROM 
TraceTable;

That's all there is to it. You can use this simple example and expand it into something useful for your organization. Here are the two scripts, as promised:

Script part one: Run this as a single unit:

/* Set Up the Variables Needed for the trace */
DECLARE @traceHandle INT; 
DECLARE @traceID INT;
DECLARE @maxFileSize bigint; 
DECLARE @traceFile NVARCHAR(128); 
DECLARE @onBit bit;
/* Set values */ 
SET @maxFileSize = 5; 
SET @traceFile = ’C:\temp\TestTrace’; 
SET @onBit = 1; 
/* Create a trace */
EXEC @traceHandle = sp_trace_create @traceID OUTPUT 
, 0
, @traceFile
, @maxFileSize
, NULL;
/* Set the events and data to collect */ 
EXEC sp_trace_setevent @traceID
, 40
, 1
, @onBit;
EXEC sp_trace_setevent @traceID
, 40
, 12
, @onBit; 
EXEC sp_trace_setevent @traceID
, 40
, 13
, @onBit;
/* Set a filter for the database to include databaseId = 5 */ 
EXEC sp_trace_setfilter @traceID
, 3
, 1
, 0
, 5;
/* Exclude SQL Profiler, which is in effect what we’re running 
EXEC sp_trace_setfilter 2 @traceID
, 10
, 0
, 7
, ’SQL Profiler’;
*/
/* Start the trace */
EXEC sp_trace_setstatus @traceID, 1;
/* Show the trace id */
SELECT @traceID;

Here is part two: Run this and change the numbers as needed for the trace ID:

/* Stop the trace */ 
EXEC sp_trace_setstatus 2, 0
/* Close the trace */ 
EXEC sp_trace_setstatus 2, 2
/* Load into a new table */ 
SELECT * 
INTO TraceTable 
FROM ::fn_trace_gettable(’C:\temp\TestTrace.trc’, DEFAULT);
/* Get the trace */
SELECT * 
FROM 
TraceTable;
/* Clean up */
DROP TABLE TraceTable;

InformIT Articles and Sample Chapters

One of the processes and tools that can use SQL Trace is the database tuning wizard. You can find more about that here.

Online Resources

The Microsoft SQL Server reference for SQL Trace is here.