Can anyone share your tips and guidance in configuring Windows Server for SQL Server 2008 Enterprise running on 64 GB RAM with dual quad core Xeon 5670 ?

in the Windows Performance option should I select "programs" of "background services" ?


  • You've got a long way to go if you're only looking there. That setting gives priorities to Windows Services (of which SQL Server will be just one) over client-driven applications. This is a default and recommended setting for Windows Server and shouldn't change. You're going to need to look at many factors, including storage, RAM configuration, power management and much more.
    – user3914
    Mar 14 '11 at 3:40

For SQL Server you can choose Background Services. But there a lot of more setting you need to configure:

  • To enable SQL Server to use large pages, enable the Lock pages in memory user right assignment for the account that will run the SQL Server: From the Group Policy MMC snap-in (Gpedit.msc), navigate to Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment. Double-click Lock pages in memory and add the accounts that have credentials to run SQL Server.
  • You can set a fixed amount of memory for the SQL Server process to use. About 3% of the total available memory is used for the system, and another 1% is used for memory management structures. SQL Server can use the rest of available memory, but not more. The following equation is available to calculate total memory to be used by SQL Server: TotalMemory – (1%memory * (numa_nodes)) – 3%memory – 1GB memory
  • Set CPU affinity for the SQL process: Set affinity mask to partition the SQL process on specific cores. To set affinity on more than 32 logical processors, use affinity64 mask. Starting with SQL Server 2008 R2, you can apply equivalent settings for configuring CPU affinity on as many as 256 logical processors using the ALTER SERVER CONFIGURATION SET PROCESS AFFINITY Data Definition Language (DDL) TSQL statement as the sp_configure affinity mask options are announced for deprecation. Use the ‘alter server configuration set process affinity cpu =’ command to set affinity to the desired range of processors for each k-group, separated by comma.

This are just a few settings, in this whitepaper you will find more settings you need to configure (choose the ones that you find relevant): Performance Tuning Guidelines for Windows Server 2008 R2


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.