1.1 What Is Performance Tuning?

What is the goal of tuning an SQL Server database? The goal is to improve performance until acceptable levels are reached. Acceptable levels can be defined in a number of ways. For a large online transaction processing (OLTP) application the performance goal might be to provide sub-second response time for critical transactions and to provide a response time of less than 2 seconds for 95% of the other main transactions. For some systems, typically batch systems, acceptable performance might be measured in throughput. For example, a settlement system may define acceptable performance in terms of the number of trades settled per hour. For an overnight batch suite acceptable performance might be that it must finish before the business day starts.

Whatever the system, designing for performance should start early in the design process and continue after the application has gone live. Performance tuning is not an one-off process but an iterative process during which response time is measured, tuning performed and response time measured again.

There is no right way to design a database as there are likely to be a number of possible approaches and all these may be perfectly valid. It is sometimes said that performance tuning is an art not a science. This may be true but it is important to undertake performance tuning experiments under the same kind of rigorous controlled conditions in which scientific experiments are performed. Measurements should be taken before and after any modification and these should be...