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
