Database Reference
In-Depth Information
Begin by installing the Distributed Replay Controller service onto a machine. There is no interface for the
Distributed Replay utility. Instead, you'll use XML configuration files to take control of the different parts of the
Distributed Replay architecture. You can use the distributed playback for various tasks, such as basic query playback,
server-side cursors, or prepared server statements. Since I'm primarily covering query tuning, I'm focus on the
queries and prepared server statements (also known as
parameterized queries
). This defines a particular set of events
that must be captured. I'll cover how to do that in the next section.
Once the information is captured in a trace file, you will have to run that file through the preprocess event
using the Distributed Replay Controller. This modifies the basic trace data into a different format that can be used to
distribute to the various Distributed Replay Client machines. You can then fire off a replay process. The reformatted
data is sent to the clients, which in turn will create queries to run against the target server. You can capture another
trace output from the client machines to see exactly which calls they made, as well as the I/O and CPU of those calls.
Presumably you'll also set up standard monitoring on the target server in order to see how the load you are generating
impacts that server.
When you go to run the system against your server, you can choose one of two types of playback: Synchronization
mode or Stress mode. In Synchronization mode, you will get an exact copy of the original playback, although you
can affect the amount of idle time on the system. This is good for precise performance tuning because it helps you
understand how the system is working, especially if you're making changes to structures, indexes, or T-SQL code.
Stress mode doesn't run in any particular order, except within a single connection, where queries will be streamed
in the correct order. In this case, the calls are made as fast as the client machines can make them—in any order—as
fast as the server can receive them. In short, it performs a stress test. This is useful for testing database designs or
hardware installations.
Capturing Data with the Server-Side Trace
Using trace events to capture data is similar to capturing query executions with Extended Events. To support the
Distributed Replay process, you'll need to capture some specific events and specific columns for those events. If you
want to build your own trace events, you need to go after the events listed in Table
24-1
.
Table 24-1.
Events to Capture
Events
Columns
Prepare SQL
Exec Prepared SQL
SQL:BatchStarting
SQL:BatchCompleted
RPC:Starting
RPC:Completed
RPC Output Parameter
Audit Login
Audit Logout
Existing Connection
Server-side Cursor
Server-side prepared SQL
Event Class
EventSequence
TextData
Application Name
LoginName
DatabaseName
Database ID
HostName
Binary Data
SPID
Start Time
EndTime
IsSystem
