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 principles. Two critical principles are the logical and physical structure of the data and the inherent differences in the application of the database. For example, different demands are made by an online transaction processing (OLTP) environment than are made by a decision support (DSS) environment. A DSS environment often needs a heavily optimized I/O subsystem to keep up with the massive amounts of data retrieval (or reads) it will perform. An OLTP transactional environment will need an I/O subsystem optimized for more of a balance between read and write operations.

In many customer environments, testing to scale with the actual application while in production is not possible. We recommend using an application load generator such as Quests' Benchmark Factory, Idera's SQLscaler, or MS TPC-B Benchmark Kit (ftp://ftp.microsoft.com/bussys/sql/unsup-ed/benchmark-kit) to simulate the loads of the targeted production data-tier environment. This technique allows offline measuring and tuning the system before you introduce it into the production environment.

Further, the use of this technique in a troubleshooting environment will allow you to compartmentalize specific pieces of the overall system to be tested individually. As an example, using the load-generator approach lets you reduce unknowns or variables from a performance-tuning equation by addressing each component on an individual basis (hardware, database, and application).

System Harmony is the Goal

Scaling an application and its database is dependent on the harmony of the memory, disk I/O, network, and processors, as shown in Figure 12-1. A well-designed system balances these components and should allow the system (the application and data tiers) to sustain a run rate of greater than 80-percent processor usage.

Figure 12-1

Of these resource components, a processor bottleneck on a well-tuned system (application\database) is the least problematic because it has the simplest resolution: Add more processors or upgrade the processor speed/technology.