Professional SQL Server® 2005 Performance Tuning

7.1. Considerations before Adjusting Server Level Settings

Throughout the book, we've talked about many aspects of performance tuning for SQL Server. Normally, you should try not to tinker too much with server settings, as SQL Server is self-configuring for the most part.

A common approach many people take when they are asked to troubleshoot a performance issue is to jump right into things, and they forget to look into some more obvious areas. Instead, there are some things to keep in mind before you dive right into the problem. Depending on the situation you are facing, you may ask some other seemingly obvious questions just to confirm or rule out various considerations:

1. Is SQL Server the only application running on the server box? Running SQL Server is a memory-and CPU-intensive process. If you share it with a domain controller, Exchange, HR, or some other enterprise applications, it probably cannot perform optimally.

2. Have you analyzed some CPU, Memory, and I/O counters? If you are running a mission-critical, resource-intensive database on an underpowered server, no amount of server tweaking and tuning can fix the problem.

3. Related to the question above, do you have a good physical design of the data? Is data placed appropriately on different spindles and I/O paths so you can maximize simultaneous I/O? Is your application designed appropriately (having short transactions, appropriate isolation levels, set operations as opposed to processing one row at a time, useful indexes, and ...