本文共 12243 字，大约阅读时间需要 40 分钟。
WHY You Stress Test Drives HOW You Stress Test
The reasons for Stress Testing vary. You may be trying to determine the apparent cause of poor performance on a production server, or trying to plan for an increase of users, or trying to workout a new application that is not yet "live" to determine if it will be able to handle a production load. The reasons vary, but your specific reason will drive your approach to stress testing. For instance, your approach will vary greatly between a pre-production test and one for a live server.
Production Environments
If it is a performance problem in a production environment that you're dealing with, you can first try to determine your problem using Performance Counters sampled throughout your busy times of day (if possible). I realize that this is a Stress Testing article and I'm starting with a non-Stress Test approach, but in the real-world it is easier to start with performance monitoring before trying to gain approval for stress testing a production server. By comparing the gathered performance counter values with generally accepted thresholds you may be able to figure out your problem without running a Stress Test. We will cover some Microsoft listed thresholds later in the article. You may want to perform your monitoring several times during the week, as the load on Monday might be different from Wednesday's load.
One more note about stand-alone monitoring before we move on to our main focus: at times it can be beneficial to gather samples over a 24 hour period or even just at night if day-time sampling is forbidden. Sometimes your late-night SQL Server Jobs and back-ups can provide some insight. For instance, you may be running back-ups from across the network and notice that your Network Interfaces have less than favorable throughput.
If you can't take samples during peak times in your production environment, you will be best served by running off-hour stress tests using the tool of your choice to create the load. In this case you are still working out the hardware/software configurations on the server in question and capturing performance counter values that can lead you to the source of the problem if one exists. If the captured performance counter values do not lead to insight, then concentrating on the execution times of application stored procedures and scripts may help uncover the problem - perhaps dead-locking, or simply bad indexing. If needed, you can open up SQL Profiler for in-depth tracing information while your stress test is running.
If you're looking to see how your production environment will scale with an increase in users, then stress testing is the way to go. You can dial-up virtual users to hammer your server and monitor its response. In this scenario, as well as all others, you want to gather Performance Monitor samples and execution times. This is also a great way to validate the need for new hardware, walking into your boss' office with a stress test report shows that you have done your homework.
Non-Production Environments
For pre-production tests or any tests that won't be held in a live production environment, Stress Testing your database can be invaluable. You will want to run your tests in a production-like environment. Simulating that can be difficult, but with automated stress testing packages you can attempt to mimic the amount of load that your servers will face and scale that load up to really make your server work. Commercial Stress Testing applications like Agilist's SQL Stress Test or Quest's Benchmark Factory enable you to create a large number of virtual users that will execute the SQL commands of your choice. A good Stress Testing package will create separate threads for each virtual user and will avoid connection pooling. In a non-production environment you typically have a lot of freedom, nevertheless, there are still questions that you will want to think about for testing in both Production and Non-Production environments.
Pre-Stress Test Questions
Here are some questions to ask prior to running your tests:
A Good Stress Test is Measurable
Throwing a hail storm of statements at your SQL Server is only part of a productive Stress Test. First and foremost, the test must be measurable. So while giving our server a workout we must gather performance related statistics. Usually we'll gather Performance Counters and if possible the actual execution times of the statements themselves. You can gather Performance Counter statistics using Microsoft's Performance Monitor (PerfMon) or the 3rd party tool of your choice.
For these statistics to mean anything we must be able to measure them against something, usually these "somethings" are baselines that we have created some time in the past or accepted thresholds. Baselines are simply previously saved results that we can compare against. We also can compare our tests against thresholds listed by Microsoft and industry professionals as mentioned previously. You can create a baseline the first time you run a test, simply save your results and now you magically have a measurable baseline that can be recalled for future comparisons. As time goes by you will want to periodically create baselines for comparison purposes. Baselines are also great for trending and determining growth rates.
Which Performance Counters To Use?
When choosing Performance Counters we don't just want to know how our SQL Server software is performing, we also want to know how our hardware and network is performing. The best list of core counters that I have come across, and have been using for years, come from an article by Brad McGehee entitled "How to Perform a SQL Server Performance Audit ". I experiment with some other counters but always use the ones mentioned in the article as the foundation. The counters are:
After monitoring your server, you will need to measure your captured counter values against trusted thresholds so that you know whether or not a problem may be at hand. I compiled a list of thresholds from Microsoft resources such as TechNet and others that match the list of Counters above. Below are the MS values along with some comments. When comparing your values to these you should always ask yourself if the value that you have collected was sustained over a period of time or if was just a spike - sustained values are obviously much more appropriate for comparison.
Additional Threshold and Monitoring Resources
There are many good articles that deal with monitoring activity and thresholds. Listed below are some worthwhile resources:
Anthony Bressi is owner of which specializes in software for DBA's and SQL Server developers. Mr. Bressi has over 9 years of hands-on experience in the Microsoft SQL Server development environment.
本文转自 Fanr_Zh 博客园博客，原文链接：http://www.cnblogs.com/Amaranthus/archive/2011/09/16/2178596.html，如需转载请自行联系原作者