I am performing stress testing on SQL Server 2008 with JMeter.
我正在使用JMeter对SQL Server 2008执行压力测试。
I wish to improve a stored procedure that has to serve 20 requests per second.
我希望改进每秒必须提供20个请求的存储过程。
The procedure takes an xml parameter and returns an xml result.
该过程采用xml参数并返回xml结果。
Should I use only one parameter value or test multiple scenarios?
我应该只使用一个参数值还是测试多个场景?
My main doubts are:
我的主要疑虑是:
- recompilations of the procedure execution plan (this may slow down the procedure)
- extraction of data from disk (not all necessary data may be hold in the main memory)
程序执行计划的重新编译(这可能会减慢程序)
从磁盘中提取数据(并非所有必要的数据都可以保存在主存储器中)
1 个解决方案
#1
3
Designing a realistic Stress Test/Load Test in SQL Server is an art.
在SQL Server中设计逼真的压力测试/负载测试是一门艺术。
There are many factors that can impact performance:
有许多因素会影响性能:
-
Hardware: You need to run your tests against the the same hardware that you have defined your target (20 call per second). This includes disk configuration, redundancy, clustering, ... This is not always possible so you need to make it as close as possible however the more different your test environment becomes, the more unrealistic results can be. This means, for example, if you use 2 CPUs instead of 4, you cannot adjust the parameters accordingly.
硬件:您需要针对已定义目标的相同硬件(每秒20次调用)运行测试。这包括磁盘配置,冗余,集群,......这并不总是可行的,因此您需要尽可能接近它,但是测试环境越不同,结果就越不现实。这意味着,例如,如果使用2个CPU而不是4个,则无法相应地调整参数。
-
Data load: in terms of number of the records you need to test, it is ideal to have around 30%-40% more of the maximum rows you expect in the tables.
数据加载:根据您需要测试的记录数量,理想情况下,您可以在表中预期的最大行数的30%-40%左右。
-
Data and index distribution: It is a common mistake to load the server with a preset or completely random data. Both are wrong. The distribution of the values need to be realistic. For example distribution of the marital status is not the same across all possible values so you need to design your data generation to include this.
数据和索引分发:使用预设或完全随机的数据加载服务器是一个常见错误。两者都错了。价值的分配必须是现实的。例如,婚姻状况的分布在所有可能的值上都不相同,因此您需要设计数据生成以包含此值。
-
Index fragmentation: this is a tough one. Normally indexes are rebuilt overnight, but during the course of the day, indexes become fragmented so the performance can be very different during those times.
索引碎片:这是一个艰难的。通常情况下,索引会在一夜之间重建,但在一天中,索引会变得支离破碎,因此在这些时间内性能会有很大差异。
-
Concurrent load: A server could provide you with 20 requests per second, if it is the only call you are making to the database but as soon as you start making other calls, it all falls to pieces. The load need to include other related parts of the system.
并发加载:服务器可以为您提供每秒20个请求,如果它是您对数据库进行的唯一调用,但只要您开始进行其他调用,它就会崩溃。负载需要包括系统的其他相关部分。
-
Operation Load: It is absolutely no point to make 20 calls per second if the requests are all the same. You need to use Data Generation techniques to make the requests realistic not purely random.
操作负载:如果请求完全相同,则每秒进行20次呼叫绝对没有意义。您需要使用数据生成技术来使请求变得现实而不是纯粹随机。
If you are using C#, I have done this tool a while back which might help you with creating realistic random data.
如果你正在使用C#,我已经做了这个工具,这可能会帮助你创建逼真的随机数据。
#1
3
Designing a realistic Stress Test/Load Test in SQL Server is an art.
在SQL Server中设计逼真的压力测试/负载测试是一门艺术。
There are many factors that can impact performance:
有许多因素会影响性能:
-
Hardware: You need to run your tests against the the same hardware that you have defined your target (20 call per second). This includes disk configuration, redundancy, clustering, ... This is not always possible so you need to make it as close as possible however the more different your test environment becomes, the more unrealistic results can be. This means, for example, if you use 2 CPUs instead of 4, you cannot adjust the parameters accordingly.
硬件:您需要针对已定义目标的相同硬件(每秒20次调用)运行测试。这包括磁盘配置,冗余,集群,......这并不总是可行的,因此您需要尽可能接近它,但是测试环境越不同,结果就越不现实。这意味着,例如,如果使用2个CPU而不是4个,则无法相应地调整参数。
-
Data load: in terms of number of the records you need to test, it is ideal to have around 30%-40% more of the maximum rows you expect in the tables.
数据加载:根据您需要测试的记录数量,理想情况下,您可以在表中预期的最大行数的30%-40%左右。
-
Data and index distribution: It is a common mistake to load the server with a preset or completely random data. Both are wrong. The distribution of the values need to be realistic. For example distribution of the marital status is not the same across all possible values so you need to design your data generation to include this.
数据和索引分发:使用预设或完全随机的数据加载服务器是一个常见错误。两者都错了。价值的分配必须是现实的。例如,婚姻状况的分布在所有可能的值上都不相同,因此您需要设计数据生成以包含此值。
-
Index fragmentation: this is a tough one. Normally indexes are rebuilt overnight, but during the course of the day, indexes become fragmented so the performance can be very different during those times.
索引碎片:这是一个艰难的。通常情况下,索引会在一夜之间重建,但在一天中,索引会变得支离破碎,因此在这些时间内性能会有很大差异。
-
Concurrent load: A server could provide you with 20 requests per second, if it is the only call you are making to the database but as soon as you start making other calls, it all falls to pieces. The load need to include other related parts of the system.
并发加载:服务器可以为您提供每秒20个请求,如果它是您对数据库进行的唯一调用,但只要您开始进行其他调用,它就会崩溃。负载需要包括系统的其他相关部分。
-
Operation Load: It is absolutely no point to make 20 calls per second if the requests are all the same. You need to use Data Generation techniques to make the requests realistic not purely random.
操作负载:如果请求完全相同,则每秒进行20次呼叫绝对没有意义。您需要使用数据生成技术来使请求变得现实而不是纯粹随机。
If you are using C#, I have done this tool a while back which might help you with creating realistic random data.
如果你正在使用C#,我已经做了这个工具,这可能会帮助你创建逼真的随机数据。