Tuning SQL Server 2005 on Windows Integrity Servers

Page 8 of 13
maximum write performance on the log. This checkbox can be selected while the system is
running without stopping the server. The performance change is immediate and does not
require a reboot.
6. Click OK and select the device again to verify that this box is still checked.
Note: Any change in log disk hardware or cache configuration can cause the OS to
unselect this checkbox.
Segregate Network processing from SQL processing
Use the affinity mask and affinity64 mask options to associate a thread with a specific
processor and specify which processors SQL Server will use. You should exclude SQL Server activity
from processors to which the OS has assigned a NIC workload.
Note: Soft NUMA (discussed below) can also accomplish this.
Before you change the setting of affinity mask (default 0), keep in mind that the OS assigns deferred
procedure call (DPC) activity associated with NICs to the highest numbered processor in the system.
In systems with more than one active NIC, each additional card’s activity is assigned to the next
highest numbered processor. For example, an 8-processor system with 2 NICs has DPCs for the NICs
assigned to processor 7 and processor 6.
Set application-dependent SQL parameters
Use the sp_configure system stored procedure to optimize resources. To modify the advanced
configuration options we recommend that you first set the show advanced options property, then
RECONFIGURE and restart the SQL Server instance.
1. sp_configure ‘show advanced options’, 1
2. GO
3. RECONFIGURE
For OLTP Workloads
Set ‘max degree of parallelism’ to 1: This option limits the number of processors used in parallel plan
execution. If you are using the sp_configure procedure to change the setting, you must first set show
advanced options to 1. The setting takes effect immediately without a SQL instance stop and restart.
NOTE: Higher values can be used, of course, but OLTP performance will degrade as this value is set
to higher levels.
Check Log Drive 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 the performance of the
system. A quick check with Perfmon can determine if this is true.
– Log Write service times should be very low (about 1ms). If not, then the cache could be
disabled, either within the array or in Windows.