Tuning SQL Server Configuration - Professional SQL Server 2005 Performance Tuning - page 171

Tuning SQL Server
Configuration
Unlike other major RDBMS products on the market such as Oracle 9i and 10 g , which have hundreds
of parameters to tweak, SQL Server, for the most part, is self-configuring. That means that the
default setting will work best for most scenarios.
Having said that, there are some configuration settings you can tweak, after careful analysis of the
application, performance counters, SQL traces/profiling, and DMVs data, that warrant such action.
If you need help with performance monitoring, SQL Server trace and profiling, and DMVs, please
refer to the relevant chapters in this topic for detailed discussion on those subjects.
In this chapter, we will discuss ways on how to tweak some advanced SQL Server settings to
maximize performance. We will start fromwhere and how to find existing SQL Server configuration
settings. Next we will examine some important server settings, what they mean, and implications
of changing them to different values.
As mentioned earlier, SQL Server is self-tuning for the most part. Only change server configuration
settings when you have solid evidence that it really helps performance.
Considerations before Adjusting Server
Level Settings
Throughout the topic, 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