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
16
You must also check the transaction log latency and write size. Since every transaction committed in SQL must be written
and committed to the log, the SQL log can easily become a bottleneck and limit system performance. A quick check with the
Windows Performance Monitor utility can easily verify this.
Log Write Service times should be very low (about 1ms). If they are not, the cache could be disabled, either within the array
or in Windows. Average Log Write Sizes of greater than 30K can also indicate a bottleneck, possibly caused by a disabled
cache. Or it can indicate that more log disks or a larger cache size is required.
If a software RAID0 stripe across multiple arrays is used for the log, it may sometimes appear that no log bottleneck exists,
when in fact one does. If the sum of the average Log QueueDepth across all of the Log’s RAID arrays multiplied by the
average log write size approaches (or is greater than) 64 KB, you might have a log bottleneck. Measuring this requires that
the log be on separate LUNs from the rest of the database.
Remember to spread all tables over multiple files and over multiple disks (the more spindles the better). Do not software-
stripe LUNs that are already hardware-striped either. While this makes them easier to manage, it degrades performance
considerably.
Drive considerations
Use the following guidelines regarding database storage:
• As noted above, if database storage latency is greater than 2 to 3 times what is was unloaded, you should add more
storage.
• Keep random IOPS to <120/sec/spindle for 15KRPM drives and 100/sec/spindle for 10KRPM drives.
• For Gigabit NICs, keep packets below 30K/sec. Use the sp_configure stored procedure to set Lightweight Pooling
to 1.
• When Lightweight Pooling is set to 1, SQL Server switches to Fibre Mode scheduling. In the event of excessive context
switching, Lightweight Pooling provides better throughput by performing context switching inline, which reduces
user/kernel mode context transitions.
Note
Fibre Mode is generally not effective with CPU utilization below 80 percent on a few or all CPUs.
• By default, SQL Server uses one thread per active SPID or user process. These threads work in a pooled configuration to
keep the number of threads manageable. The advanced Lightweight Pooling configuration option (sometimes referred to
as Fibre mode) uses Windows
Lightweight pooling restrictions
• SQL Server Agent (Microsoft KB303287): When SQL Server runs in Lightweight Pooling mode (or Fibre mode) and the DTC
service is started, unexpected behavior may occur. SQL Server Agent might not execute any jobs.
• DTC (Microsoft KB303287): If DTC operations are required on the server, the SQL Server instance should always run in
Thread mode. In other words, Lightweight Pooling should be set to 0 (zero). Microsoft strongly recommends that you run
the SQL Server instance in Thread mode when DTC is needed. If you use Lightweight Pooling/Fibre mode on a system that
does not specifically need it, performance often degrades.
• sp_xml_and *sp_OA* functionality (Microsoft KB322884): Microsoft does not support the use of Microsoft Common
Language Runtime (CLR) extended stored procedures or OLE Automation with any libraries loaded to run in the SQL
Server memory space. CLR only uses thread-based scheduling and does not support fibre-mode scheduling. In later
versions of SQL, you cannot load CLR by using extended stored procedures or sp_OA stored procedures.
For more information about the lightweight pooling option, see msdn.microsoft.com/en-us/library/ms178074.aspx.
Other considerations
Use NUMA support to reduce remote memory access. This can improve performance up to 60%, especially when combined
with Connection Affinity. In general, segregate connections and thus data locality in a given node (for example, by region or
department, or any other logical division that makes sense for the application). When NUMA support is enabled, SQL
attempts to create a thread’s data structures in the same NUMA node, thereby reducing remote memory accesses.
In SQL Server, NUMA is enabled by default. All versions of SQL Server mentioned in this white paper also provide a Soft
NUMA feature, again enabled by default, which enables more precise control as described below.
Use Connection Affinity to take further advantage of SQL Server NUMA features. With Connection Affinity, a SQL connection
from the client is assigned affinity to a specific NUMA node. This assigns data structures to that NUMA node, further