SQL Server becomes sluggish or appears to stall on 64 bit installations
I would like to make you aware of corrections you should apply to your 64 bit installations of SQL Server to avoid sluggish behavior. Microsoft has encountered reports from a few seconds up to minutes in duration where it appears SQL Server is not processing requests.
On 64 bit systems calls to VirtualQuery are more expensive than on 32 bit systems due to the increased virtual address range. While handling internal exception conditions both SQL Server and the operating system can invoke VirtualQuery code path.
SQL Server often uses C++ exceptions to handle various error conditions. When this occurs a RaiseException is invoked. The operating system uses the routine RtlLookupFunctionTable to ensure the throw location is a valid address in the image (for DEP security reasons). When multiple exceptions are being handled it is possible that VirtualQuery can be invoked by the RtlLookupFunctionTable.
SQL Server also attempts to validate an internal structure when the exception is caught. Older versions of SQL Server use VirtualQuery as well.
The VirtualQuery activity can take out the virtual address (VAS) lock of the process and stall memory activites such as VirtualAlloc and VirtualFree. We have seen the VAS lock be held for extended periods of time leading to sluggish SQL Server behavior.
· SQL Server 2005 no longer used VirtualQuery and always used QueryWorkingSetEx when exposed by the OS.
· SQL Server 2000 back ported the 2005 logic in build 8.00.2187 (SP4 + QFE)
· The OS was changed to use QueryWorkingSetEx instead of VirtualQuery in the RtlLookupFunctionTable in a Windows 2003 SP1 + QFE build. Refer to Microsoft Knowledge Base Articles 919341 and 922658
· Windows also corrects a scheduling issue that can lead to erratic behavior of user mode applications. SQL Server can report the 17883, latch timeout and various other scheduling related errors. Refer to Knowledge Base article 908675 for more details.
Not only is the virtual address range larger on 64 bit installations but a second issue applies to 64 bit installations.
On X86 floating point exceptions are not raised by the instruction that encountered the exception. Instead they are raised by the next floating point operation attempt. Knowing this the compiler and optimizer code lines wrap floating point operations with an immediate checks. When a floating point operation is performed, during compile, the floating point exception bit it checked, handled and cleared if necessary. This avoids floating point exceptions. The compiler handles the condition with a few instructions and assigns proper defaults.
On 64 bit floating point exceptions are raised by the instruction that causes the exception. This means that on 64 bit installations the exception is actually raised and has to be caught. This causes RaiseException, RtlLookupFunctionTable and other code lines to be invoked on 64 bit that won’t be invoked on X86. Since raising of an exception can trigger calls to VirtualQuery the pressure on the VAS lock can be increased on 64 bit installations.
It is not uncommon for the compiler/optimizer to encounter 100s of floating point exceptions while attempting to evaluate the possible plan combinations. Using the newer builds of SQL Server and the operating system the pressure on the VAS is significantly reduced.
Bob Dorr
Senior SQL Server Escalation Engineer