SQL Server Magazine

Step 4: Create a Stored Procedure from the Trace Definition Script
Next, use Query Analyzer to open the .sql script file you saved in Step 3. This file will be the heart of the scheduled trace. I don't like to run big chunks of SQL in a scheduled job, and it's easier for me to see what's going on in my databases when everything is in a stored procedure. Therefore, I recommend converting this script into a stored procedure. Create the procedure with two parameters: a file name and the trace ID. The trace ID will be an output parameter; you don't need this ID to build the scheduled trace, but if you ever want to run the stored procedure separately, you might want to know the trace ID.

When you convert the script to a stored procedure, you need to make two important modifications to the script. First, replace the "InsertFileNameHere" string in the sp_trace_create statement with the parameter you created for the file name. Second, add a RAISERROR statement to return an error message if sp_trace_create throws an error. (For style points, you can also get rid of the goto.) Listing 1 shows the converted script. Note that the sp_trace_create stored procedure expects a file name without an extension—it will fail if you append the extension yourself—whereas the fn_trace_gettable() function requires you to add the .trc extension.

Step 5: Create a Table from the Trace Output File
The next task is to create a table in a SQL Server database to hold the results of your scheduled trace. SQL Server has a built-in function, fn_trace_gettable(), that lets you load the data from a trace output file into a table. Executing the following SQL statement with the appropriate name of your trace output file creates the table without you having to type the entire CREATE TABLE statement:

SELECT *
INTO myTrace
FROM 
  ::fn_trace_gettable('D:\sqlbackup\trace_dump_00.trc', default)
/* Setting the second parameter 
  as default tells the function 
  to get all the files if the 
  trace created more than one 
  file. */

You created this trace file earlier by running the trace and saving the output. No matter what kind of trace you just ran, all the possible trace-result columns (e.g., start time, end time, number of reads, duration) will be in this trace file, and this table will accommodate a variety of future traces.

Step 6: Create a Table to Hold the Trace Path and File Name
This next step will make sense after you create the scheduled jobs. To avoid having to type the trace file path and name in several places, you can create a table to hold the path and file names. This table also lets you easily change where SQL Server writes the traces to and lets you set up and manage multiple traces and jobs. Creating the table is simple; just use the following code:

CREATE TABLE tracer(
  tid int primary key 
     identity(1,1) NOT NULL,
  output_path_file varchar(100) 
     NOT NULL,
  output_path_file_ext AS 
     (output_path_file + '.trc'))

After creating this table, insert a row with the full path and file name, omitting the .trc extension. My INSERT statement looks like this:

INSERT tracer (output_path_file)
VALUES 
  ('\\networkShare\folder\myTraceOutput')

The myTraceOutput part is the name of the file that will store the results of the duration trace. Leave off the extension; the computed column you added to the tracer table will add it for you. I highly recommend placing the trace file on a network drive or a drive SQL Server isn't using, especially when running this trace on a production server. You need to evaluate the best arrangement for your environment. Remember that the goal for this particular trace is performance tuning, so whatever your configuration, try not to skew the results. In any trace, keep the number of events you capture and data columns you return to a minimum. Note that the SQL Server Service account you use to run SQL Server must have write permission wherever you direct your trace output. The scheduled job must run under an account that has full permissions for the trace file as well.

Step 7: Schedule a Job to Run Your Trace
Now, create two scheduled jobs to run and stop the trace. Web Listing 1 contains the T-SQL scripts that create these two jobs. The first job has only one step, which starts the trace by running the _duration_trace stored procedure that you created earlier. Before running the stored procedure, the job reads the tracer table to get the full path and file name of the output file the trace will create. The step looks like this:

— Job 1, Step 1
DECLARE @path_file nvarchar(200)
SELECT 
  @path_file=output_path_file 
  FROM tracer WHERE tid = 1
EXEC _duration_trace @path_file, null

The WHERE tid = 1 clause returns the tracer table row that contains the name of the trace output file.

Setting the schedule for this job depends on how often you want to run the trace. The first job only starts the trace; the second job ends it. Without the second job, the trace would run forever, so make sure the second scheduled job is working. I recommend using the built-in notification features for scheduled jobs so that you can receive a page or email if the second job fails. Also, the sp_trace_create procedure lets you set a maximum file size, which can keep a runaway job from consuming all your disk space.

The first job starts the trace and completes almost immediately; it doesn't run for the duration of the trace. To see whether the trace is running, use this SQL statement in Query Analyzer:

SELECT * FROM 
  ::fn_trace_getinfo(default)
— Default or 0 will return all 
— running traces.

This statement returns multiple rows for each trace running on that SQL Server. Each trace will have a unique value in the traceid column. This traceid is the value the stored procedure returns when it runs the sp_trace_create procedure. If no traces are running, the statement returns zero rows.

Prev. page     1 [2] 3     next page