Windows Server 2003 Service Pack 2 provides performance improvement for SQL Server 2005...

Windows Server 2003 Service Pack 2 provides performance improvement for SQL Server 2005 under intensive workloads. Service Pack 2 installation installs these improvements by default (https://www.microsoft.com/technet/windowsserver/sp2/overview.mspx).

Two major improvement are introduced:

1.    Fix to help those SQL workloads that show a high kernel time and at least some of it is due to network traffic. The optimization fixes a bug within the sockets API where Winsock would signal an internal event even if SQL didn’t provide it one and nobody was waiting on that. Signalling that event would cause a dispatch event that required holding the system wide dispatch lock. This lead to unnecessary spinlocks and excessive kernel time. The fix was to introduce a new sockopt called FO_SKIP_SET_EVENT, which indicates to the socket API that it’s unnecessary to wait and signal on the internal event.

This specific issue came up with Winsock IO, where during data-sends on a SAP workload, the data-sends always ended up taking the fast-IO path, and the IO always completed inline with STATUS_SUCCESS. Removing the redundant KeSetEvent on this path gave us 7% back! This turned out to be a huge win for SQL Server 2005.

2.    Timer processing in Windows is performed at DISPATCH_LEVEL on CPU #0 and requires the Dispatch Lock, so it can have significant performance impact on enterprise systems running heavy loads. In 64-bit Windows Server 2003 SP1 and x64, an optimization to break up timer processing into batches contains a well-hidden bug. A simple code change produces a 30% increase in tpmC on a 32-socket NUMA Intel x64 system.