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

Characterizing aWorkload for Replay
Characterizing a SQL Server database workload is a way to abstract or take small samples from massive
SQL Server events. A workload should have a specific purpose, a list of measurable contents, and a
pre-defined timeframe.
Baseline(DailyPeak,SeasonalPeak)
Some businesses require the collection of performance data on a regular basis to ensure the integrity of
the service level agreement (SLA). Many answers to performance questions need transactional data for
support. To establish a baseline for an SLA, identifying measurable items in the SLA is an example of
workload characterization. Most servers in production experience daily peak hours and seasonal peaks.
In SQL Server 2005, there is no built-in feature to report daily (weekly, monthly) user transactions by
category or response time on certain user transactions. For capacity planning, to predict business growth
scenarios, it is a common practice to establish a baseline first.
Figure 10-1
Then, there are several options. For simple scenarios, trending analysis may be sufficient. Large organi-
zations might find that specialized commercial tools for analytical or simulation modeling are desirable.
You might need to rely on other business information to find seasonal peak loads. For accounting sys-
tems, monthly closing; for Internet banking, pay days or monthly payment due days; for retail stores,
holiday shopping seasons.
Using Profiler to replay workloads on a reference server provides a quick way to compare a new work-
load with a baseline workload. For example, you may want to replay a 24-hour workload on a reference
server for benchmark purposes. Later, you can replay another set of workloads and compare overall
timings for the replays.