Beginning, Microsoft® SQL Server® 2008 Administration

Chapter 11. Optimizing SQL Server

Webster's dictionary defines optimization as "an act, process, or methodology of making something as fully perfect, functional, or effective as possible." Looking at this definition in the context of SQL Server, one might say that optimization is making the best possible use of the Database Engine to minimize query response time, resource usage, and hardware stress. As you might imagine, this is not a trivial task, and in practice, it is as much art as it is science. There are so many choices, options, opinions, and techniques available that it is easy to get lost in all the rhetoric and lose sight of the goal.

My approach to optimization is not to try to tell you exactly what you need to do, but instead to share with you the knowledge and techniques that I have developed over many years as a successful database administrator and application designer. You may not agree with all of the ideas and methods that I will present, and it may well be that in your specific situation an alternative approach will yield better results. What follows is what I have found to work in the majority of cases.

In order to be successful at optimization, you need to have a clear understanding of any applications that are using your server and how they compete for limited resources. In this regard, the design of the application has a bigger long-term impact than any other factor. Time spent up front to properly design an application is more valuable than 10 times the effort ...