Step 8: Schedule a Job to Stop the Trace, Load the File, and Clean Up
The second scheduled job, which has three tasks, does most of the work. First, it stops the trace; second, it loads the output file from the trace and writes the results into the myTrace table; and third, it deletes the trace file from the hard disk.
Listing 2 shows the T-SQL script for creating the three steps. The first task calls the sp_trace_setstatus stored procedure twice—once to stop the trace and again to close it. Until these two calls are made, the trace is running and the output trace file is locked on the disk—it can't be moved or deleted while the trace is running. The first part of this task reads the parameter table (tracer) that you set up earlier to get the full path and file name of the trace output file. Getting this information from a table lets you avoid hard-coding the file name into all the job steps.
The second task also reads the file name from the tracer table into a variable but uses the output_path_file_ext computed column with the .trc extension. With the file name in the @path_file_ext variable, the following SQL statement writes the results from your trace output file into the table you created earlier:
INSERT myTrace
SELECT *
FROM
::fn_trace_gettable(@path_file_ext, default)
The third task is crucial. Using master.dbo.xp_cmdshell, you must delete the trace output file. The built-in stored procedure sp_create_trace will fail with an "Error 12" message if the file already exists on disk the next time the scheduled trace runs. The schedule of this second job, which stops the trace, determines how long your trace will run. If you schedule the first job to run at 10:10 am and you want the trace to capture 5 minutes of data, schedule the second job for 10:15 am.
Step 9: Look at the Data and Learn
Now for the fun part. The scheduled trace writes a steady stream of information about the requests sent to your SQL Server to the myTrace table, where you can easily query and analyze it. With my duration trace, I'm looking for ways to improve performance. After a few queries against the myTrace table, I can quickly see which databases the long-running processes are hitting. But best of all, I can spot trends over time and see which databases are showing performance deterioration and need more attention. Because I'm capturing the starting time of each process, I can see when the slowest processes run. I can find out which queries are doing the most reading or writing and which applications are the busiest and when. From this information, I can optimize queries, add an index, or rewrite a stored procedure to try to get it to run in less than a second.
Things to Consider
You need to consider a few things about scheduled traces. Because these traces run at set intervals, they might miss a long-running process that runs outside your scheduled time. One way to handle this limitation is to vary the schedule from day to day. If you set the trace to run for 5 minutes at 15-minute intervals beginning at 6:00 am on Mondays and Wednesdays, you could start the trace at 6:07 am on Tuesdays and Thursdays. The process still isn't random, but you can at least design in some variety.
In a perfect world, you'd set up these traces to run against your development database during the testing phase to spot problems before moving the database into production. But realistically, you might need to run these traces against production servers because a test server will almost never have the same load as your production server. Minimizing the number of events and data columns in your trace will help reduce the stress on a production server. Another way to mitigate this stress is to set up a linked server and write the SQL Server trace results table to that server. In addition, writing the trace output file to a network drive helps by not putting stress on the SQL Server disk subsystem.
The benefits of automated traces are well worth the effort. With performance data in a table, you can easily learn about the applications accessing your server. Just running a simple query that counts the number of processes that take more than 1 second and groups them by database ID can show which databases have the most long-running processes. The code in Web Listing 2 creates a lookup table, courtesy of Kalen Delaney, which will let you translate those cryptic EventClass numbers you see when running Profiler traces into descriptions. She also included a sample SQL statement that lets you quickly retrieve averages by EventClass.
Following these steps creates a framework for running any trace you can dream up. Create the trace with Profiler, save the SQL script as a stored procedure, and plug it into the job you've already set up. Profiler can capture so much information about SQL Server—from stored-procedure recompiles to lock acquisitions to database-file growth. No matter what you're looking to improve, setting up these scheduled traces can give you consistent insight into your databases and help you learn more about your SQL Server.
End of Article
Prev. page
1
2
[3]
next page -->
