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

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: WebLOAD (load testing) Web Performance Testing Tools QuotiumPRO Empirix e-Load Quest Benchmark Factory for Databases Mercury LoadRunner Knowledgestorm Scandiasoft DbValidator WAPT 3.0 OpenLoad

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:   Go back

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 2005 TechTarget

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Microsoft SQL Server Performance Monitoring and Tuning Using traces in SQL Server Profiler SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server Clearing the Windows page file and its effect on server performance Optimizing SQL Server indexes –- even when they're not your indexes Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary