7.4. Operating system configuration
An often overlooked configuration area for SQL Server is configuration of the operating system itself. In this section, we'll take a look at configuration from a Windows perspective, focusing on items that can have a measurable impact on SQL Server performance and stability.
7.4.1. Running services
In chapters 4 and 6 we covered the importance of only installing the required SQL Server features. It's very easy during installation to select all features on the off chance that they may be required in the future. As a result, people often end up with Analysis Services, Reporting Services, Integration Services, and full-text search, all of which create Windows Services that run on startup.
In addition to these SQL Services, there are a host of other Windows Services such as IIS that may be running that are possibly not required. It's outside the scope of this book to list and describe which ones are candidates for disabling, but IIS is worth a special mention. In SQL Server 2005, IIS was required for Reporting Services. Fortunately, this is no longer the case in SQL Server 2008. If you installed IIS solely for the purpose of supporting Reporting Services, you won't need it if you're running SQL Server 2008.
Disabling nonessential services is good from a performance perspective as well as an effective security practice in reducing the attack surface area.
7.4.2. Processor scheduling
Accessed via the Control Panel, the advanced options of System Properties let you choose between adjusting processor resources to favor foreground applications or background services. As a background service, SQL Server obviously benefits from this option being set to Background Services, as shown in figure 7.9.
Figure 7.9. The options under Processor Scheduling should be adjusted for the best performance of background services.
7.4.3. Network protocols
As you saw in chapter 6, disabling unnecessary network protocols is important from both a security and performance optimization perspective.
7.4.4. Page file location
By default, Windows will create a page file that is 1.5 times the size of the physical memory up to a maximum of 4GB (on 32-bit systems). On a server dedicated to SQL Server with sufficient memory, particularly those with the Lock Pages in Memory option, the amount of paging should be negligible. As a result, page file configuration isn't a task that most DBAs spend a lot of time considering.
That being said, for systems with large amounts of memory, one of the benefits of configuring a larger than normal page file is to assist in advanced troubleshooting scenarios. For example, if SQL Server crashes, the memory dump can be inspected for later diagnosis. Configuring a page file large enough for such a memory dump is worth considering for such cases.