Professional Microsoft® SQL Server® 2008 Administration

11.1. What Every DBA Needs to Know about Performance

In this chapter, we lay out a lot of specific hardware recommendations that will enable you to improve the performance of your system. However, shaving milliseconds off of a transaction time isn't always worth the amount of time and hardware budget you spend to accomplish that goal. Frequently, good planning up front is worth more than clever optimization later. Always keep the following three things in mind: define your system performance targets, start tuning early, and focus on these performance requirements:

11.1.1. The Performance Tuning Cycle

Performance tuning is an iterative process and ideally starts at the beginning of the design process. Too often performance and optimization are tacked on at the end. Obtaining optimal performance starts with configuring the server, continues with designing an efficient schema and specifying optimized SQL statements, which leads to optimal index selection. The monitoring and analysis of performance can then feed back to any point in this process, right back to changes to server configuration or schema design. This system is illustrated in Figure 11-1.

Figure 11-1. Figure 11-1

You start with your first best guess about how you think your application is going to work and what resources you think it's going to use and plan accordingly. Most often, it's only a best guess, as you really ...