In my previous post in this tutorial (here), 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:
- How to use the graphical Profiler to generate a T-SQL script for a SQL trace
- Customizing this trace script and run a SQL trace
- Open the captured trace using the graphical Profiler
- 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:
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:
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:
- 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)
- 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)
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:MyFolderMyTrace. 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:BRTraceBRT', @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:
- Specify a stop-time (refer above on how to specify a stop-time)
- 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 sp_trace_create
- Using the sp_trace_status system stored procedure to stop the trace manually
- Convert your most used Profiler trace template into a T-SQL script definition by hand
- 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?
- 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
Until we meet next time,