数据库压力测试思路

原文是E文，做一下翻译并且提取一些重要的思路。

1.定义业务规格

• What are your most critical business processes and how often are they processed (e.g. number of sales activities per day, number of client requests per day, etc.)
• 最重要的业务流程是什么，使用频率如何
• What is regarded (required) as acceptable response times from the online application? How about reports?
• 可以接受的在线应用程序的响应时间是多少，报告又如何
• How much data must be kept available on the live database (over 1 month, 1 year, 5 years?). Can data be archived? How and when?
• 数据在live数据库中要保存多久，可不可以归档，如何归档，什么时候归档
• How many concurrent application users are expected?
• 并发的用户数是多少
• Are there periods of more than average usage? (E.g. Daily between 9:00 A.M. and 10:00 A.M. /yearly ’round about Christmas.) What business processes will be impacted the most?
• 业务的高峰期在什么时候，那些流程会被影响
• How much data latency is acceptable for reports?
• 报表数据延迟，多少时间内可以接受
• Etc.
• 等等

2.分析业务

• How many inserts/updates/deletes/selects will be performed on the most important database objects when the database is averagely stressed? How much when there is a period of more than average usage?
• 在平均负载的情况下，在重要的数据库对象中，有多少insert，delete，select会发生。在业务高峰期有是多少
• How large will your database core objects become (over 1 month, 1 year, 5 years)?
• 若干时间后，数据库的核心对象会变得多大？
• How many concurrent database users are expected? (This is not equal to application users if connection pooling is implemented.)
• 数据库的并发量是多大
• What should be regarded as acceptable database response times? For online requests/for reporting requests?
• 可以接受的数据库响应时间是多少

3.定义磁盘子系统的需求

• 可以使用sqlio 来测试 磁盘子系统
• 也可以使用SQLIOStress
• 如何达到标准文章并没有提及，可以根据业务繁忙时段，io的吞吐量也确定，多少才能满足要求

4.为准备脚本

• Ask a business user to perform a realistic work process as he would in real life situation, including realistic intervals between tasks.
• 咨询终端用户实际工作流程，真实的工作状况，包括任务之间的时间间隔
• Trace this activity using SQLProfiler (only textdata and starttime are important)
• 使用SQLProfiler跟踪操作
• Replace variables with placeholders for parameters that can be defined at random. (This is where you wanted all database interactions to be performed using stored procedures.)
• 使用随机数来代替用户操作的变量
• Add think times to the script.
• 增加延迟
• 相关的操作作者给出了代码，可以参考原文

5.添加虚拟用户

• 相关的操作作者给出了代码，可以参考原文

6.添加测试数据

• 可以使用DTS或者SSIS初始化数据

7.监控

　　I cover this topic in my previous article on Monitoring.

8.迭代测试

　　It is important to run multiple iterations using different parameter combinations but you also need to perform multiple iterations of the same scenario to avoid accidental external impact.

　　使用不同的参数组合迭代测试十分重要，你也需要执行在同一个场景下执行迭代测试来避免意外情况的影响。

原文：SQL Server Monitoring in 8 Steps: Lessons From the Field - 02 Aug 2005