“Extreme Performance” is a term that a colleague and I coined in 2002 to describe part of my philosophy for building highly scalable and well performing database systems. I’ve written about this topic over the past few years and I want to revisit it again briefly this week. Why? Well, the more things change, the more they stay the same. SQL Server and every other major database product on the market are significantly more advanced than they were 5 years ago, but I bet people still make many of the same simple mistakes over and over again.

My philosophy of extreme performance tuning has two core elements. First, anticipate the fact that eventually someone will push your code well beyond what you expected. You might never need that extra level of performance, but it’s substantially easier to design an efficient application than to a fix a poorly performing application after it’s deployed. Second, never forget that it’s easier to scale the application layer than it is to scale the database layer. Let’s explore how each of these tenets of extreme performance relate to practical, day-to-day system building.

First, notice that I didn’t include any particular benchmarks for transactions per second in my definition. “Extreme” implies the uber high end of the scalability range, but the problems I discuss here happen in “normal” systems just as much as they apply to systems that consume as much electricity as the house I live in.

I live by the first rule of extreme performance tuning: Anticipate that people will push your application beyond what you expect. I make my living as a SQL Server consultant. Many of the problems I’ve helped customers solve over the past 15 years were born during innocuous conversations among a development team, when someone uttered something to the effect of, “Oh, we don’t’ need to worry about that--this system will never be used that heavily.” How do you avoid the problem of under-performance? It’s mostly common sense. Don’t take shortcuts that you know are poor performance choices simply because you’ll ever create a bottleneck. And test your systems with a reasonable amount of data. Yes, doing a comprehensive performance test is very hard. No, third-party vendors have still not found a way to make it easy. To be honest, part of me is thankful. Most of my billable hours are in support of helping customers solve their performance problems, and if vendors ever make it too easy to avoid problems in the first place, I might have to get a real job.

You might look at my second rule of extreme performance tuning and ask, “Is the application layer easier to scale than the database?” Absolutely. Oracle has made a few more strides in the area of grid computing than SQL Server, but no database vendor has yet achieved the easy scalability of a Web farm, in which you can scale up simply by adding new servers. Scaling up a database server often requires throwing away your old server for a brand new server. Designing an application that allows the database to scale is hard. But always try to remember that an application server farm is easier to scale than a database server. I won’t mention names, but I worked with one customer whose system designers (who, fortunately, are long gone) put almost everything--and I do mean everything--in the database. This practice led to a custom queue-management system, extensive use of cursors, wonderfully complex metadata, and a host of other problems aren’t well handled in a database. Even if a database could do the tasks this one was assigned, doing work in a database that can be done in the application layer violates the principles of extreme scalability.

I’ve seen customers hit a brick wall on back-end database performance that requires substantial code rewriting, which can be difficult and painful. If certain high-overhead stored procedures had been initially designed as middle-tier components, the solution would have been to add another commodity-priced Web server to the farm. Although these stored procedures might initially yield higher throughput than middle-tier components, sometimes it’s better to heavily weight the future implications of “what if.”

Part of me wants to say that avoiding the vast majority of performance problems really is just common sense, planning, and testing. However, I’ve been doing performance tuning for more than 10 years and have helped several hundred customers in large and small ways. I know that avoiding performance problems isn’t as simple as saying “It’s common sense.” Most of the customers I work with are very smart, and if avoiding performance problems was simple, they’d have already done it. But it’s true that some of the basic rules are easier than you might think. I’ve repeatedly found that adhering to the philosophy of extreme scalability is relatively easy and can prevent a large number of performance mishaps from cropping up in typical database environments. Just like Mikey in the cereal commercials, try it--you’ll like it!