Chapter 7. Configuring SQL Server

As with security configuration, you should make server and database configuration changes with care. Most of the configurable settings in SQL Server work best when left at their default values. You should measure any changes from the defaults for performance and stability impacts in a controlled testing environment before introducing those changes into production.

Like any change, configuration changes should be accompanied by a detailed change log. It's often tempting to flick the switch on a configuration setting on the assumption that it may improve performance. Without an adequate change log and a controlled process to measure the effect of the change, uncontrolled configuration changes make future troubleshooting complex. For example, as part of investigating a performance problem, you may notice an odd configuration setting. Was this change made for a reason? Did the change help or hinder performance? Without a change log including the recorded impact, such questions are difficult to answer, particularly in large environments with many SQL Server instances.

In this chapter we drill down into some of the configurable settings and look at situations in which alternate configurations may lead to performance and administrative advantages. We begin the chapter by looking at memory configuration options, including the differences between 32- and 64-bit environments. We then look at other configuration categories: CPU, SQL Server settings, and operating system configuration.