In my previous post in this tutorial, I introduced the system stored procedures that would allow us to create a SQL Trace, define events and data columns to capture and start the trace using nothing but pure T-SQL code. Today, we will see the following:

1. How to use the graphical Profiler to generate a T-SQL script for a SQL trace
2. Customizing this trace script and run a SQL trace
3. Open the captured trace using the graphical Profiler
4. Explore the option to schedule traces

The exercises to this chapter are very interesting and essential in getting a firm grasp over SQL Traces.

How to use the graphical Profiler to generate a T-SQL script for a SQL trace

As I was writing my previous post, I realized that manually identifying and writing down the integer representations of the events and data columns to capture is a labour intensive activity. I generally do not write the T-SQL scripts myself. I leverage the graphical Profiler to generate the script for me. Here’s how I generate the script using the Profiler:

Connect to any instance of SQL Server and identify the events and data-columns to be captured (because we will only be using it to generate the T-SQL script for the trace, it does not matter what server we connect to)

Identify any filters that need to be applied

Start and quickly stop the trace

Go to File –> Export –> Script Trace Definition and choose the version for which the script is to be generated

Save the script to a location of your choice

Here is a snapshot of the T-SQL script definition that was generated (actual code has not been provided intentionally):

As you can see, we can find usages of the following system stored procedures within the script:

1. sptrace_create
2. sptrace_setevent
3. sptrace_setfilter
4. sptrace_setstatus

Customizing this trace script and run a SQL trace

The generated T-SQL trace script file is not ready to be consumed directly. Here are a couple of things that we need to change before we can go ahead and start tracing:

1. Provide a path to store the trace files. As mentioned in the trace file, the text “InsertFileNameHere” needs to be replaced by the actual path and file name (without the .trc extension, which the SQL Server will automatically add for us)
2. Check the value of @maxfilesize For this demo, I will reduce the max. file size to 1MB for each file (using such a low value in your production use might result in performance issues)
3. Add (if required) the @stoptime parameter (by default, this is set to NULL, which would cause the trace to continue until the trace is manually stopped or until the server shuts down)

The changes that I have made to my trace are therefore:

declare @stoptime datetime
set @maxfilesize = 1
set @stoptime = '2012-03-07 19:40:00.000'

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 2, N'C:\BRTrace\BRT', @maxfilesize, @stoptime

To start the trace, we simply need to run the entire script by hitting the “Execute” button on the Query Editor bar in SSMS. To generate a load to capture by the trace, I would simply run the script I used in one of my previous posts.

Open the captured trace using the graphical Profiler

Once the trace completes at the specified time, you can see that the trace files are now available for analysis at the mentioned path. These files are regular .trc files and can be opened using the SQL Profiler for offline analysis, thereby leveraging the power of graphical tools for analysis and pure T-SQL for data capture with minimum overhead.

Explore the option to schedule traces

Traces are generally used for troubleshooting performance issues on a SQL Server instance. Performance issues may crop up as part of a nightly job, a ETL (Extract-Transfer-Load) operation during a BI process or during a process where it may be desirable to schedule traces for analysis the next day or so.

We have two options for scheduling traces:

1. Specify a stop-time (refer above on how to specify a stop-time)

2. Schedule traces using the SQL Server Agent, which would simply involve:

   • Starting the trace using the SQL Server Agent jobs

   • Specifying a stop time in the T-SQL trace definition and using it in the system stored procedure sptracecreate

   • Using the sptracestatus system stored procedure to stop the trace manually

Exercises:

1. Convert your most used Profiler trace template into a T-SQL script definition by hand
2. Convert your most used Profiler trace template into a T-SQL script definition using the Profiler. Check for differences between the one by hand and the one generated by the Profiler. Why are they different?

3. Customize the T-SQL script definition generated by either of the approaches to have:

   Max. file size of 7MB each

   Enable rollover and shutting down of the server in case of an error

   Limit to 5 roll-over files

4. Run the customized template and capture the trace for a test workload

5. Analyze the workload using the Profiler
6. Schedule a trace using the SQL Server Agent and the T-SQL script definition files

References/Further Reading:

1. Using a SQL Trace
2. Describing events by using data columns
3. Scheduling traces