Most IT professionals recognize that addressing performance problems during development is faster, easier, and less expensive than waiting until after you roll out your application to production. But development teams that take performance tuning seriously during the early stages of development are rare in the business world for several reasons. One, performance-tuning and scalability-testing experts are scarce. Two, tools for managing database performance tests are expensive and difficult to use and configure. And finally, given that development projects frequently fall behind schedule, no one has time to add extra tasks to the process. I can't solve all these problems today, but I have a tip that might solve a surprising number of performance problems before they ever see the light of day.
It's no secret that I'm a big SQL Server Profiler fan. In my opinion, Profiler is the single most powerful tool in your performance-tuning arsenal because it's the best way to see what's happening inside your system. However, the SQL Server community often sees Profiler as a DBA tool. You rarely see developers actively using Profiler to hunt down performance problems. I know that you might not want to give your developers the ability to run Profiler in production for many reasons--most importantly because Profiler requires the user to have sa rights. However, there are good reasons for developers to have sa control over the boxes they're developing for, and performance tuning is one of them.
You're even less likely to find developers incorporating Profiler into their unit-testing strategies. Based on a decade of performance-tuning experience, I believe that easy-to-fix problems--such as missing indexes and excessive numbers of round-trips to the server from one screen--cause more than half of a production system's performance problems. These problems often go undiscovered because even if performance isn't perfect, it's typically good enough that no one bothers to improve it.
Most IT shops investigate such problems only if they cause noticeable and serious performance problems for their user community. However, developers can find and solve many of these easy-to-fix problems by performing basic Profiler analysis against each of their use-case scenarios during unit testing. Too often I've conducted a performance audit for a company and heard, "Wow, I had no idea the application did that!" No matter what "that" is, you can usually easily avoid the problem by running Profiler as part of the unit test. For example, one customer recently had a single search result in more than 5000 round-trips to the server. Although 5000 round-trips from one screen might be fine for one user at a time, it doesn't scale well. Incorporating Profiler into a unit-test strategy will quickly and easily uncover problems like this one.
Of course, running Profiler won't automatically solve all your performance-tuning problems. Developers need to know what to look for when analyzing Profiler output. Your internal SQL Server experts should be able to compile a simple list of tuning dos and don'ts that you can share with development teams seeking to integrate Profiler into their unit-testing strategies.
I encourage every development team I work with to embrace the idea that tuning isn't simply the DBA's domain. The application doesn't work if it doesn't meet basic performance requirements. If the application doesn't work, it should pass through testing. I'm also not suggesting that DBAs should outsource performance tuning to developers and play golf for the rest of their lives. Not all tuning and scalability issues can be detected during unit testing. DBAs and developers need to learn to partner together more effectively to prevent performance problems. Integrating simple performance tests, based on Profiler analysis, into a unit-test strategy is a great first step toward that partnership.