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
13
Use SQL Server startup flags
There are several SQL Server Trace flags you can use to increase SQL performance. These have proven useful in large
systems like the DL980.
• T834 − Use Large Pages
When combined with Lock Pages in Memory option described above, this flag forces SQL to use 2 MB pages for the buffer
cache instead of the default 4 KB pages. This reduces Translation Lookaside Buffer (TLB) size and contention, as well as
simplifies memory management by reducing the number of pages to be tracked and manipulated. This flag is particularly
recommended on systems where SQL server is the main (or only) application running.
Depending upon the workload, enabling these settings may also increase system performance:
• T652 − Disable Page Prefetching Scans
Use only for OLTP workloads, and only if the system is more than 80 percent busy.
• T661 − Disable Ghost Removal Processes
Eliminates the overhead of deleted records being cleaned up by a ghost removal process. If the system is quite active,
these deleted records are re-used quickly anyway, so this flag eliminates that overhead.
• T8744 − Disable Prefetch for Ranges
Disables prefetching for nested loops.
Ensure SQL Server starts up immediately after system boot
SQL Server should be started as soon as possible after the system boots, especially when using the Use Large Page trace
flag described above. This maximizes memory organization for SQL server and ensures that large pages can be easily
acquired from the available system memory.
Enable write caching on the log disk
The bigger the system, the greater the number of transactions and need for a cached log file for the database. The amount
of data in the cached log file is proportional to the database. For example, on a Superdome, if the storage array is too slow
to handle the array stream, you should enable write caching on the log drive.
As a rule of thumb, on a server with eight or more processors you need significantly more storage for the cached log file.
Use an appropriate disk configuration tool to configure and enable the hardware cache.
To enable Write Caching on the log disk, follow these steps:
1. Right-click My Computer and select Manage.
2. Click Device Manager and expand Disk Drives.
3. Locate the log disk(s) and right-click each device entry. If you are using Secure Path or some other multi-path disk
software, the correct disk may be a virtual device.
4. For each entry, right-click, select Properties, and click the Policies tab.
5. Verify the Optimize for performance radio button is selected.
6. Below that, verify that the Enable write caching on the disk and Enable advanced performance boxes are checked (see
Figure 2 on page 14).