Professional Microsoft® SQL Server® 2008 Administration

14.2. Tuning Process

There is always room for tuning, but it is very important that you have enough data to start with. Therefore, you need to take a baseline of the system's performance and compare against that baseline so that you know where to start. Chapter 13 has details on getting the baseline. Just because a process is slow doesn't mean that you have to start tuning SQL statements. There are many basic things you need to do first, such as configure the SQL Server 2008 database, and make sure tempdb and the log files are on their own drives. It is very important to get the server configuration correct. See Chapters 11 and 12 for details about configuring your server and database for optimal performance. There is also a white paper on tempdb that we recommend you read as well. This document is for SQL Server 2005, but it's useful for SQL Server 2008 as well: www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx.

Don't overlook the obvious. For example, suppose you notice that performance is suddenly pretty bad on your server. If you have done a baseline and that doesn't show much change in performance, then it is unlikely that the sudden performance change was caused by an application in most cases, unless a new patch for your application caused some performance changes. In that case, look at your server configuration and see if that changed recently. Sometimes you merely run out of disk space, especially on drives where your page files reside, and that will ...