7.5. Best practice considerations: configuring SQL Server

In almost all cases, the best configuration setting is the default. Any change should be based on sound advice, and tested for performance and stability in an accurate load-testing environment before implementation in production.

• The reason for a configuration change is sometimes easy to forget. To assist in future troubleshooting, each configuration change should be recorded in a change log with at least the following details: date and time of the change, person executing the change, script location (if change was script based), and the results of the change (observed performance, any errors, etc.).

• In a 32-bit platform, the /3GB switch (or BCDEdit/increaseuserva) can be used to enable an extra 1GB of RAM for SQL Server, but only for systems containing up to 16GB of RAM. Before using this option, ensure the impacts are fully understood and measured in an accurate load-testing environment.

• The Lock Pages in Memory right should be assigned to the SQL Server service account for both 32-bit AWE systems and 64-bit systems.

• The Maximum Server Memory value should be specified, particularly for multi-instance servers, clustered servers, shared servers, and those using the Lock Pages in Memory setting.

• When setting the value for Maximum Server Memory, consider the other system components such as drivers, backup software, and the MemToLeave area. Even on dedicated servers, their memory requirements need to be considered in creating a stable environment for SQL Server.

• In OLTP systems with more than eight CPUs, using a maximum MAXDOP setting of 8 is recommended in most cases. The effort to split and rejoin a query across more than eight CPUs often outweighs the benefits of parallelism.

• On NUMA systems, the MAXDOP setting shouldn't exceed the number of CPUs available to the NUMA node(s) used by the SQL Server instance.

• Only the applications, services, and network protocols required should be installed and running. Performance and security improves as a result of disabling items not required.

• Ensure the Windows Server is configured for Background Services in System Properties.

• Although paging shouldn't be present in any significant volume on a well-configured SQL Server, consider placing the page file on a separate disk from the operating system and SQL data files.

Additional information on the best practices covered in this chapter can be found online at http://www.sqlCrunch.com/config.

Until SQL Server 2008, one of the challenges with server configuration was maintaining good configuration settings across a large number of servers, particularly in enterprise environments with lots of instances and many DBAs of varying skill levels. Policy-based management, covered in the next chapter, has made this task significantly easier.