Three weeks ago, I described a SQL Server system-design philosophy that I call "designing for extreme performance." Many of you asked me to clarify my thoughts in a few key areas, and I'm going to start that process this week. I'll continue to share my extreme-performance strategies—and those you send me—over the next few months. You can reread the original commentary, "Designing Databases for Extreme Performance," at http://www.sqlmag.com/articles/index.cfm?articleid=26139 , but here's a quick recap if you're pressed for time: Designing for extreme performance means
- assuming system demands will eventually be greater than you ever anticipated.
- attempting to design scalability into the system by letting the system scale out across multiple servers when possible.
- realizing that scaling out the Web farm is much easier than scaling out the database layer.
The logical conclusion of those three strategies often leads me to place complex business logic and processing on the Web tier rather than the database tier. I sometimes place this logic and processing on the Web tier even when response time during below-peak conditions would actually be faster if I moved more logic to the database tier. Some of you asked why. And the short answer is that, eventually, the database server might run out of horsepower. If that happens, scaling out the stateless Web farm, as I said earlier, is much easier than scaling out the database server.
Here is another set of comments and questions from a reader who cut directly to the heart of the matter and summed up several other people's responses:
"You gave the impression that it's more scalable to place processing in components in the Web server instead of in stored procedures. I agree for some functions, but I don't agree for all functions. It depends upon what the function is. Does it need more data from the database to do its job? If so, it will probably not help scalability to move the work from the database. The database will have to work hard anyway. It might even hurt scalability. I guess we agree that 'it depends,' and \[you probably\] tried to provoke \[us\] a bit by giving 'one single truth' for all situations."
I'll let you in on a little secret. The only absolute truth in the world of database performance tuning is "it depends." Anyone who tells you that something is always the correct answer is probably a) lying or b) ignorant. (Notice that I said "probably"—a tricky way of saying "it depends." <g>) The nice thing about testing a tuning hypothesis is that you can often easily observe a before and after result. Unfortunately, we typically have to make educated guesses about the best answer early in the design phase, before we can fully test a specific theory. In those cases, we have to rely on rules of thumb that have served us well in past projects. So, when should you put processing logic in the Web tier rather than the database? It depends. However, the following rules of thumb are valuable to keep in your bag of tricks:
- Consider breaking up a procedure and distributing it to the Web if the procedure is getting too big. What's too big? It depends on your environment.
- Consider moving processing to the Web if a) the move doesn't add additional round-trips to the server and b) the incremental cost in response time for a single user isn't significant.
- Consider moving logic to the Web tier if the procedure performs a lot of non-data-access processing (that is, something other than an INSERT, UPDATE, DELETE, or SELECT statement).
- Consider moving logic to the Web tier if the procedure is consuming huge amounts of CPU time and could be run by large numbers of users concurrently as the system scales. Database servers have four main hardware resources: disk, network, memory, and CPU. The first three are reasonably easy to scale out for an online transaction processing (OLTP) system. But scaling out CPUs quickly becomes impossible without buying a new server.
Incorporate these design principles into your applications. Live by the law of "it depends." Always test a tuning hypothesis with your own data, from your own applications, on your own servers. Your end users will thank you for it.
