Professional SQL Server® 2005 Performance Tuning

10.1. Capturing Workloads for Replay

Workload data contains what really happened in a SQL Server instance. With captured data, you can analyze usage patterns of database objects and system resource consumptions for database objects.

The scope of the workload should be specific. For example, a workload is configured for collecting a specific database application daily load. The workload contents should be reasonably easy to analyze by a person or by a computer program. The workload's file size should be easy to transport through networks.

10.1.1. Characterizing a Workload 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.

10.1.1.1. Baseline (Daily Peak, Seasonal Peak)

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 ...