Load testing is the most accurate, yet also the most expensive way to design hardware for a database server. Load testing involves testing the workload of a database application on a prototype server at continuously increasing intensity levels. Typically, software that simulates an increasing number of user loads and concurrent request transactions is used to initiate stress. In this way, various aspects of server performance can be calculated as the server handles the load; based on the information collected, the hardware design of the server can be adjusted accordingly.
When designing a load test, it's important to ensure proper distribution of transactions and ensure that the load is applied under usage patterns similar to the production environment. To observe the impact of execution, randomly generated one or more programs in gradually increasing loads cannot accurately predict the effect of actual workloads on a real server.
One of the most critical data points determined in load testing is the position of the threshold in the performance curve, where the performance of the prototype server drops very quickly and becomes hard to accept. Performance thresholds refer to specific metrics of sub-system usage related to thresholds in the performance curve, such as CPU utilization at 75% or disk utilization at 85%. A more direct sign of SQL Server bottlenecks is whether the counter Batch Requests/sec (in the SQL Server: SQL Server object) reaches a maximum limit after rising.
The result of a load test is a performance curve that shows how the response time of the database server decreases as the user load increases. Of course, every database server has its own specific curve, but the general pattern of such curves is the same. First, as the number of users increases, the server handles the increased load without any difficulty. This is the range of optimal performance, which ends at the point where the server can handle the maximum number of users with no response cost in terms of performance. The maximum efficiency point occurs when there are 100 concurrent users. In a production environment, a database application should execute within the range of optimal performance and as close as possible to the maximum efficiency point.
After this point, when new users are added, performance will gradually weaken in an area called the stress zone, indicating that tuning, upgrading, or replacing the performance of the production server should be considered. Next, after passing the performance threshold, the execution performance will deteriorate significantly, and the system will become unstable. For the performance curve corresponding to the database server, the performance turning point occurs when there are 175 concurrent users. Thus, 175 is the maximum relative number of users this database server can handle in a production environment without further optimization or updates.
In addition to helping you determine the thresholds of the performance curve, load testing can also confirm the SLA requirements that the hardware can meet within a certain future period. For example, if the SLA stipulates that CPU usage must remain below 75%, you should add more onboard CPUs to the server so that CPU usage does not exceed 75% over the next three years.
Although using load testing to design server capacity has obvious advantages, this method is extremely costly for most companies (except large organizations). Companies with smaller budgets that want to conduct capacity planning can consider using workload analysis methods.
Original article from Anhui Server Data Recovery Center: http://www.ahserver.com/plus/view-63-1.html