清单:负载测试 SQL Server性能调整的魔法棒
作者:Jeremy Kadlec(Edgewood Solutions公司)
没有什么事情比这更糟了,采纳一个新的应用程序,而它的性能是如此的糟糕,导致业务在一片惊叫声中暂停。这并不是新出现的现象;这就是事实,我经常遇到这样的事实。我打赌你也曾经经历过。那么如何防止这些性能问题,有什么解决方案?
在这些应用程序“在惊叫声中暂停”的情况中,应用程序通常都已经在按照功能性分配的短暂的测试时间内进行过适当的测试了。但是充分吗?由于竞争和全球经济的原因,迅速应用于业务意味着只进行了最小化的测试。同样,最小化的测试也成为按时将应用程序发布给用户群体的可接受的风险之一。另一点需要注意的是,负载测试太贵了,所以修正产品问题的成本也是也通常是比较小的——至少在发布应用程序的时候它不会妨碍进展和创新性,对不对?
不幸的是,我具有相反的经历:企业为运行得极其糟糕的应用程序所付出的有形的和无形的代价超过了采用可靠的负载测试方法在人力、程序,以及技术上面的投资。因此,我愿意提供以下对你的应用程序进行负载测试的清单,它将会成为防止你的SQL Server性能调整问题的魔法弹。
清单:负载测试——
SQL Server
性能调整的魔法棒
项目管理
平衡项目管理方法学,确保项目按照一个确定的过程进行,尽量减少或者避免遗漏步骤,包括对每个版本的应用程序进行负载测试。
管理层的支持
在负载测试的需求和收益的基础上与
IT
经理们合作。获得他们对正确分配时间为每个应用程序进行负载测试的支持。
性能需求
了解系统应该支持的用户、事务、数据集和可接收的处理时间等信息。如果这些信息没法确定,与企业进行沟通,了解在现有的硬件和软件基础上,应用程序的生命周期中能够支持多少用户。
用户期望
确定用户和业务期望——为了保证应用程序正确运行,需要进行功能性和负载的测试。
任务时间表
按照你的团队具有的严格意义上的资源,计算完成这些负载测试和代码检查任务所需的时间,在你的项目计划中,为其安排充分的时间。
负载测试工具
对你要用来进行负载测试的工具进行标准化。以下是一个简短的列表,如果你对产品不熟悉的话。点击每一条都会进入该工具的说明页。
负载测试过程
将你的应用程序负载测试过程标准化,建立文档模板,对测试过程的流线进行编码,同时记录应用程序涉及的结果。考虑在软件版本的基础上采用迭代的方式。
代码检查
所有的代码都会让开发人员和数据库管理员进行测试,以确认明显的功能,但是还要安排第二个人来进行测试。如果你没有预算请一位全职或者兼职的测试人员的话,那么可以考虑让开发团队互相检查。如果你有测试人员的预算,那么就让你的团队一起来为应用程序增加负载吧。
将负载测试过程流水线化
当应用程序发布为产品时确定一些对系统性能起作用的因素,然后将其用在每一次的应用程序发布版本上。
需要进行负载测试的时候进行负载测试
虽然对每个应用程序都进行负载测试是很好的,但是成本通常是有限的。因此,从那些出现过性能问题的单个应用程序开始,并且从这个应用程序中吸取教训。将知识扩展开来,在你的企业中创建一个最好的实践指南,以此在应用程序的开发阶段防止性能问题的出现,这也会负载测试过程流水线化。
通过各种方式,避免将性能问题引入产品环境中。实际情况是,不能每个场景都经过测试,但是可以定位你的应用程序中的核心内容,并且随着你的经验积累而日益提高。考虑将上面的清单作为进行负载测试的开始点,并且与你的团队讨论一下什么选项可以获得高性能。你在负载测试方面有什么经验?告诉我,然后调整状态,准备阅读下一部分的SQL Server性能调整贴士,我们将会提供更多本领域的观察报告,并且简单推荐可能改善整体系统性能的方法。
作者简介:
Jeremy Kadlec
Jeremy Kadlec
是
Edgewood Solutions
公司的首席数据库工程师。
Edgewood Solutions
公司是一家术服务企业,提供各种专业服务和针对微软
SQL Server
的产品解决方案。
Kadlec
撰写了很多文章,并且在地区
SQL Server
用户群和全国
SQL PASS
中发表了大量介绍
. Kadlec
是
SearchSQLServer.com
网站的性能调整专家。
您可以
点击这里向他提问
。
==============英==文==版==================================
Checklist: Load testing -- the magic bullet for SQL Server performance tuning | |
Few things are worse than rolling out a new application and its performance is so bad that it brings the business to a screeching halt. This is not a new phenomenon; it's a reality that I have seen from time to time. I am willing to bet that you have experienced it, too. So what is the solution for preventing these performance problems?
Often in these "screeching halt" application situations, the application tested properly for the brief amount of time allotted for functional testing. Was that sufficient? The quickly moving pace of business, based on competition and the global economy, means testing is minimized. Along the same lines, minimal testing becomes an acceptable risk for delivering the application to the user community on time. Another notion is that load testing is too expensive, so the cost of correcting production issues is generally less -- at least it doesn't stifle progress and innovation while delivering the application, right?
Unfortunately, I have had the opposite experience: The tangible and intangible costs to the organization for a poorly performing application outweighs the investment in people, processes and technology to leverage solid load-testing methods. As such, I would like to offer the following checklist for load testing your applications, which can be the magic bullet for preventing SQL Server performance-tuning problems.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Checklist: Load testing -- the magic bullet for SQL Server performance tuning
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
< td>
|
|
|
|
|
|
|
Project management
Leverage a project-management methodology to ensure the project follows a prescribed process, resulting in fewer or no missed steps, including load testing each version of the application. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Managerial support
Work with the IT managers on the need for and benefits of load testing. Gain their support for properly allocating time to conduct load testing for each application. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Performance requirements
Get a handle on the number of users, transactions, data sets and acceptable transaction times the system should support. If this cannot be determined, communicate to the organization how many users can be supported over the life of the application with the existing hardware and software. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
User expectations
Set expectations with the users and the business -- that in order for the application to perform properly, it needs to conduct functional and load testing. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Task scheduling
Schedule sufficient time in your project plans for load testing and code reviews with the proper resources on your team to complete these tasks. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Load testing tool
Standardize on a tool you'll use to load test your applications. Here is a short list, in case you are not familiar with any products:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Load-testing process
Standardize a process to load test your applications and build document templates and code to streamline the testing process as well as record the results as the application evolves. Consider an iterative approach based on the software releases. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Code reviews
All code should be tested by the DeveloperDBA to validate the obvious functionality, but schedule a second pair of eyes to perform the testing. Consider cross pollinating your development teams if you do not have the budget for a part time or full time tester. If you do have budget for a tester, have your team collaborate to properly load the application. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Streamline the load-testing process
Determine a few metrics to validate the system performance as the application is released to production and execute for each application release. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Load test when load testing is needed
Although it is favorable to load test every application, the cost is typically prohibitive. As such, start with a single application that has experienced performance issues and learn from this application. Spread the knowledge and create a best practices guide in your organization to prevent performance issues in the development stage of the application, which will streamline the load-testing process. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
By all means, prevent the performance problem from being introduced to the production environment. The reality is that every scenario cannot be tested, but address the core items in your application and grow with your expertise. Consider the checklist above as a starting point for load testing and discuss options with your team to achieve high performance. What have been your experiences with load testing? Let me know, then stay tuned for the next installment of SQL Server performance-tuning tips when we will offer more observations from the field and simple recommendations that will improve overall system performance.
ABOUT THE AUTHOR: |
|
Jeremy Kadlec
Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. Kadlec is the SearchSQLServer.com Performance Tuning expert. Ask him a question here. Copyright 2005TechTarget |
|