Professional Microsoft® SQL Server® 2008 Administration

12.1. Application Optimization

The first order of business for scaling SQL Server 2008 on the Windows Server platform is optimizing the application. The Pareto Principle, which states that only a few vital factors are responsible for producing most of the problems in scaling such an application, is reflected in this optimization. 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 2008 is to minimize the response time for each SQL statement and increase system throughput. This will maximize the scalability of the entire database server by reducing network-traffic latency, and optimizing disk I/O throughput and CPU processing time.

12.1.1. 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 needs an I/O subsystem optimized for more of a balance between ...