Professional SQL Server® 2005 Performance Tuning

15.4. Load Testing

Load testing an application or database is the process of exercising your would-be production system by subjecting it to the largest loads that it can handle. This can also be referred to as endurance testing as the goal of the exercise is to determine whether the production system will sustain itself under maximum load, such as the number of transactions that will be seen during peak working hours. Load testing is also used to detect any bugs that would not normally surface during cursory testing such as memory management issues, buffer issues, and I/O bottlenecks. The most important goal, however, is to determine the maximum number of transactions per second that the entire production system can handle without causing visible slowness to the users. Stress testing on the other hand is used to determine the maximum capacity of the production system. This is usually accomplished by subjecting the system to larger than usual workloads to determine the breaking point for the system. Stress testing is important because there will always be that one unthinkable day when there is complete chaos in the system and everyone seems to be entering thousands of transactions or pulling massive reports (such as at the end of the quarter).

There are many ways to load test the hardware. Some involve the use of enterprise-class load-testing applications such as Mercury Interactive, and others use simpler methods such as the SQL Server Profiler playback feature. Whatever may be ...