Database Reference
In-Depth Information
You have two options for setting up these events. First, you can use T-SQL to set up a server-side trace. Second,
you can use an external tool called Profiler. While Profiler can connect directly to your SQL Server instance, I strongly
recommend against using this tool to capture data. Profiler is best used as a way to supply a template for performing
the capture. You should use T-SQL to generate the actual server-side trace.
On a test or development machine, open Profiler and select TSQL_Replay from the Template list, as shown in
Figure
24-1
.
Figure 24-1.
The Distributed Replay trace template
Since you need a file for Distributed Replay, you'll want to save the output of the trace to file. It's the best way
to set up a server-side trace anyway, so this works out. You'll want to output to a location that has sufficient space.
Depending on the number of transactions you have to support with your system, trace files can be extremely large.
Also, it's a good idea to put a limit on the size of the files and allow them to roll over, creating new files as needed.
You'll have more files to deal with, but the operating system can actually deal with a larger number of smaller files
for writes better than it can deal with a single large file. I've found this to be true because of two things. First, with a
smaller file size, you get a quicker rollover, which means the previous file is available for processing if you need to load
it into a table or copy it to another server. Second, in my experience, it generally takes longer for writes to occur with
simple log files because the size of such files gets very large. I also suggest defining a stop time for the trace process;
again, this helps ensure you don't fill the drive you've designated for storing the trace data.
Since this is a template, the events and columns have already been selected for you. You can validate the events
and columns to ensure you are getting exactly what you need by clicking the Events Selection tab. Figure
24-2
shows
some of the events and columns, all of which are predefined for you.
