Problem solve Get help with specific problems with your technologies, process and projects.

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. What is the solution for preventing these performance problems? Load testing your applications.


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.

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.

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 Performance Tuning expert.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.