"Extreme performance" is a term that a colleague and I coined 2 years ago to define the SQL Server performance-tuning approach that we take with our consulting customers. I'd like to explain the essence of our philosophy, which might help you avoid performance-tuning mistakes down the road.
Extreme performance means two things to me. First, it means that everything you do today with an application needs to anticipate the fact that someone will eventually push your code well beyond what you intended it to do. You can more easily design an efficient application than do triage on a poorly performing application after deployment. You need to test your application with reasonably sized data sets and take adequate steps to ensure that your application can expand as needed. Everyone knows that you should design systems with performance and scalability in mind.
My principles of extreme performance include another important aspect. Two basic approaches to scalability exist: scale up or scale out. To scale up, you beef up a single SMP box. To scale out, you increase horsepower by adding new boxes to the system. Scaling out is easier than scaling up because it doesn't require you to anticipate your power needs. You simply buy another box when the time comes, or at least that's the theory. It's infinitely easier to scale out your Web farm than to scale out the database layer. I'm not going to defend that position now, but it's true.
What does scalability mean when you're designing a system for extreme performance to meet unpredictable future demands? It means that sometimes you should deploy a component on the middle tier of a Web farm rather than as a stored procedure, even if the throughput is better when you deploy the component as a stored procedure. That approach might seem counterintuitive. But here's why it makes sense. You can easily add another box to your Web farm if you hit the edge of your scalability envelope. However, scaling out the database layer can be difficult and expensive, especially when you didn't design the application with scaling out in mind.
I've seen customers encounter substantial back-end database performance decreases that require substantial code rewriting—a difficult and painful process. They could have solved the problem by adding a commodity-priced Web server to the farm if they had designed certain expensive stored procedures as middle-tier components. Sure, initial throughput might be better if they had deployed the component as a procedure, but sometimes you need to sacrifice a small short-range performance gain for long-range performance and scalability requirements.