Professional SQL Server® 2005 Performance Tuning

3.2. Types of Performance Problems

In its simplest form, performance problems have as many potential causes as the number of hardware and software components in a system. On this basis the scope of an individual problem can be quite huge, but it is possible to reduce this scope somewhat. Typically we can characterize SQL Server performance problems in one of two areas:

• Configuration-based performance problems

• Schema-based performance problems

That isn't to say that every single SQL Server performance problem you'll ever investigate will fall into one of these two areas, but it provides a useful logical grouping for most problems. The following section provides further detail on the classification of each problem area, with examples of the types of problems you can expect to see within each problem area.

3.2.1. Configuration-Based Performance Problems

SQL Server doesn't require any specialist knowledge to install and get started with databases, but this can be its downfall at times. There are often cases where server engineers are asked to install and configure SQL Server and make decisions based on assumptions or unfamiliarity with the product. Common examples include not separating data and log files onto separate disks (where performance gains can be made through Input/Output (I/O) optimization by the disk controllers reducing disk head movement. A similar example is an sp_configure option within SQL Server that calls Priority Boost, which sounds attractive, almost too good ...