Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980

Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980
12
interrupts). In general, the former is recommended unless the application requires extremely low latencies. If in doubt, a
good compromise is to set the interrupt moderation to Adaptive.
SQL Server tuning recommendations
While there are potentially many issues to consider when discussing SQL Server tuning, this white paper focuses mostly on
how to run the SQL Server engine optimally on large NUMA platforms such as the HP ProLiant DL980 server. The previous
sections already covered some of those relevant parameters found at the BIOS, Operating System, and Network levels. Now
it is time to look at SQL Server itself.
Important
SQL Server 2008 is limited to a maximum of 64 logical processors. As a reminder, K-groups are also called processor
groups. Support for systems that have more than 64 logical processors is based on the concept of a processor group, which
is a static set of up to 64 logical processors that is treated as a single scheduling entity. Processor groups are numbered
starting with 0. Systems with fewer than 64 logical processors always have a single group, K-group 0.
On systems running Windows Server 2008 R2 or Windows Server 2012 in which the operating system sees more than 64
logical processors (either with Hyper-Threading on, or when each processor has 10 cores), an instance of SQL Server 2008
will be limited to the K-group size in terms of processor count. For example, a DL980 with 8 processors of 10 cores each (for
a total of 80 logical processors) will show two K-groups of 40 processors each if optimized; therefore, SQL Server 2008
running on this system would indicate a process count of 40 in the SQL Server error log.
The K-group size will be 64 logical processors maximum, but can be adjusted by the HP DL980 System Providers, or a
BCDEdit switch, or by altering the registry as described in the MS Knowledge Base: support.microsoft.com/kb/2506384.
Use startup options to lock pages in memory
A set of default startup options is written to the registry when SQL Server is installed, but these options can be overridden.
For example, in SQL Server, locking pages for the buffers is supported by default in both SQL Server Enterprise Edition and
Developer Edition, whereby the Windows operating system no longer pages out the buffer pool memory within the SQL
Server process. However, the Windows operating system can still page out the non-buffer pool memory within the SQL
Server process. For more information about this topic, see “Microsoft Knowledge Base Article 2659143,” available at
support.microsoft.com/kb/2659143.
To enable the Lock Pages in Memory option, follow these steps:
1. Click Start > Run.
2. In the Open box, enter gpedit.msc.
3. From the Group Policy console, expand Computer Configuration > Windows Settings > Security Settings > Local
Policies.
4. Select the Users Rights Assignment checkbox. The policies are displayed in the details pane.
5. In the details pane, double-click Lock Pages in Memory.
6. In the Local Security Policy Setting dialog box, click Add.
7. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
Note
To use this capability, the user running the instance of SQL Server (typically Administrators) must have the Lock Pages in
Memory capability enabled.
When Lock Pages is used for SQL Server, an entry is logged in the SQL Server error log. You should set a “max server
memory” value for the SQL Server instance(s) to ensure the operating system keeps a portion of the RAM for its own
operation. A performance monitor helps to determine the best value here.
You also need to enable Trace Flag 845 with SQL Server 2008 R2 Standard Edition, in order to use locked pages for the
buffer pool, along with granting the SQL Server service account the Lock Pages in Memory security privilege.