Windows Server settings Background Services and using for SQL Server

Here are some related discussions.

1) Here’s a link for a discussion about the setting.

If the value was set to 0 then that means that the Foreground and background applications equally responsive. I tested it in my LAB. on my system default value was set to 2 and when changed to Background services registry value changed decimal 24. Sometime it may happen that the settings you configure via GUI doesn’t take effect properly thus registry can edited directly. If you have value 24 in registry then your system is optimized for Background Services. No need to worry then.

Look at this section below from- http://support.microsoft.com/kb/102987

========================================================

PriorityControl Entries

The PriorityControl key defines the foreground/background priority boost differential. Change this value by choosing the Tasking button in the System dialog box in Control Panel. Values are stored under this Registry path:

   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\PriorityControl
		

Win32PrioritySeparation REG_DWORD 0, 1, or 2

Default:   0x2
		

Specifies the priority to give to the application running in the forground. This application receives more process or time relative to other applications running in the background. The values here correlate to the following options in the Tasking dialog box:

Value Meaning

0          Foreground and background applications equally responsive
1          Foreground application more reponsive than background
2          Best foreground application response time
		

========================================================

Cheers!

---

Sachin Gadhave
MCP, MCSA, MCTS 

2) And here’s another one

 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