Professional SQL Server™ 2005 Administration

Chapter 12. Optimizing SQL Server 2005

Since the inception of SQL Server 7.0, the database engine has been enabled for self-tuning and managing. With the advent of SQL Server 2005, these concepts have reached new heights. When implemented on an optimized platform (as described in Chapter 11) with a properly configured SQL Server instance that has also been well maintained, SQL Server 2005 remains largely self-tuning and healing. In this chapter we introduce and discuss the SQL Server 2005 technologies needed to accomplish this feat.

Application Optimization

The first order of business for scaling SQL Server 2005 on Windows Server platform is optimizing the application. Chapter 33 of the SQL Server 2000 Resource Kit refers to this optimization as the Pareto Principle, which states that only a few vital factors are responsible for producing most of the problems in scaling such an application. If the application is not well written, getting a bigger hammer will only postpone your scalability issues rather than resolve them. Tuning an application for performance is beyond the scope of this chapter.

The goal of performance tuning SQL Server 2005 is to minimize the response time for each query and increase system throughput. This will maximize the scalability of the entire database server by reducing network-traffic latency, optimizing disk I/O throughput and CPU processing time.

Defining a Workload

A prerequisite to tuning any database environment is a thorough understanding of basic database ...