Extreme performance is a term that a colleague and I coined 5 years ago to define the SQL Server performance-tuning approach that we recommended to our consulting customers. Understanding and implementing the extreme-performance philosophy might help you avoid performance-tuning mistakes down the road. One area in particular that our philosophy is helpful is in planning for scalability. Planning with scalability in mind lets you improve performance now and will help you take advantage of emerging technology, such as the Common Language Runtime (CLR) integration in SQL Server 2005.
First, be aware that everything you do with an application today needs to anticipate someone pushing your code beyond what you intended it to do in the future. It's easier to 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. We all need to design systems with performance and scalability in mind.
There are two basic approaches to scalability: 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 future power needs; you simply buy another box when the time comes. And scaling out your Web farm is easier than scaling out the database layer. So how do you think about scalability when you're designing a system for extreme performance to meet unpredictable future demands? 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. Scaling out the database layer can be difficult and expensive, especially when you didn't design the application with scaling out in mind.
Thinking about designing for scalability brings to mind a common dilemma. I've seen customers encounter significant back-end database performance decreases that require substantial code rewriting--a difficult and painful process--when they could have solved the problem by adding a commodity-priced Web server to the farm if they'd designed certain expensive stored procedures as middle-tier components. Sure, initial throughput might be better if they'd 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. Enter the CLR...
What if you didn't have to sacrifice short-term response time for long-term scalability? What if you could cram as much processing onto the database tier as possible, then easily re-deploy certain processes to a mid-tier application server when your database began to run out of CPU steam? What if the tradeoffs between scalability and response time became a runtime deployment issue rather than a fixed-in-stone design issue? That would be cool! Theoretically, the CLR gives us that option. We could deploy .NET-based code in the CLR to reduce round trips and keep data-oriented code near the data. But, if the code was carefully architected we might later be able to re-deploy the code on the application tier if the database server starts to become resource constrained. Yes, response time for transactions might go up a bit if that happens, but presumably we could achieve greater concurrency and throughput. The CLR also makes it easy for us to overburden our database server by pushing logic to SQL Server to cut down on round trips (or whatever reason you might come up with). But don't forget: scaling the Web tier is easy, whereas scaling the database often requires throwing your existing box away in favor of a new box. Indiscriminate deployment of code to the CLR is a recipe for an overburdened SQL Server and disaster. Carefully architected solutions that let us deploy in the CLR but readily re-deploy in a CLR hosted somewhere else might provide the ability to focus on response time in the short term and scalable throughput in the long term.
Undoubtedly, it will take some time to work out the best practices and architectural recommendations that let customers design systems in this way, but the benefits could be huge. I love when I get to have my cake and eat it, too!