Capturing, Measuring, and Replaying a Workload Using SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 338

The remainder of the chapter covers the workload collection used in the three scenarios above. You
do not have to reproduce the same workload to follow through the three scenarios. It is best to focus
on how the Profiler replay feature is applied in different scenarios. The sample workload data file
( Ch10_Sample_Workload_DS2_40Threads_20Min_3SecThink.trc ) and the sample database (DS2)
used in the chapter are available for download at www.wrox.com .

Best Practice
Regardless of whether trace data is stored in trace files or a database trace table, using
Profiler to replay a trace is CPU-intensive. It is considered the best practice to use a
separate and dedicated server to replay trace data, not your target server that receives
the workload.
It is also important to configure these two servers (the server replays the trace and
the target server receives the workload) on the same network subnet with minimum
network hops and maximum throughputs.

WorkloadGeneration for CaseScenarios
Workload data for the three scenarios was prepared in the following manner. An open source tool, DVD
Store Version 2 (DS2), was used to simulate database transactions from a dedicated server. SQL Profiler
was used to define and collect a workload.

DS2 is a complete online e-commerce test application with a backend database component, a web applica-
tion layer, and driver programs. You can find details about this application and download DS2 from the
web site http://linux.dell.com/dvdstore/readme.txt . DS2 simulates an online transaction process-
ing (OLTP) workload for an order entry system. The multi-threaded driver program included in the DS2
package supports SQL Server 2000. Each thread of the OLTP driver connects to the database and makes
a series of T-SQL stored procedure calls that simulate customers logging in, browsing online items,
and making purchases. Each completed sequence of actions (logging-in, browsing, and purchasing) is
counted as a single customer order.

The DS2 package comes with a DS2 database. It has the following tables: Customers, Orders, OrderLines,
Cust_Hist, Productss, Inventory, Reorder, and Categories. DS2 comes in three standard sizes listed in
Table 10-2. We used the medium size for generating workloads in the scenarios. Since the multi-threaded
driver program has not been updated for SQL Server 2005, we installed the DS2 database in a SQL Server
2000 instance.

Table 10-2: DS2 Database Sizes

Database Size Customers Orders Products

Small 10 MB 20,000 1,000/month 10,000

Medium 1 GB 2,000,000 100,000/month 100,000

Large 100 GB 200,000,000 10,000,000/month 1,000,000