FIX: Failure to Spawn New Thread Not Handled Correctly by SQL Server and Open Data Services

ID: Q216381

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: 18595 (SQLBUG_65), 18596 (SQLBUG_65)

SYMPTOMS

On computers with 2 GB or more of physical memory, where SQL Server has been configured to use 1.5 GB or more, various unpredictable behavior may be observed when SQL Server or Open Data Services (ODS) fails to spawn a new thread. Symptoms include, but are not necessarily limited to, the following errors:

99/01/09 18:57:53.09 server SetThreadPriority failed: 6

99/02/01 09:49:46.06 ods 99/02/01 09:49:46.74 ods 99/02/01 09:49:46.76 ods ?}")?}99/02/01 09:49:46.76 ods 99/02/01 09:49:46.79 ods ?}")?}99/02/01 09:49:46.79 ods
(This message contains a repeating pattern of the date/time, a source of 'ods', followed by random garbage characters for the message text.) 1999/02/01 09:49:48.01 ods Unable to connect. The maximum number of '500' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure.

1999/02/01 09:49:48.50 kernel Not enough memory for pss allocation

1999/02/01 10:56:47.70 ods Error : 17803, Severity: 17, State: 0
1999/02/01 10:56:47.70 ods Insufficient memory available.
1999/02/01 10:56:47.70 ods OS Error : 8, Not enough storage is available to process this command.

After such an error, the system may stop responding, or users may report that they are unexpectedly disconnected from the server.

CAUSE

The server is typically misconfigured such that the SQL Server process has run out of its 2 GB (or 3 GB on Windows NT Server 4.0, Enterprise Edition) of available virtual address space. At this point, all memory allocation attempts are failing, resulting in unpredictable server behavior.

When a new thread is spawned, a 1 MB allocation is performed by the operating system for the thread's stack. When the available address space has been exhausted, these allocations will fail, causing a failure to spawn a new thread. SQL Server fails to appropriately check for this error condition, causing some of the errors and unusual behavior described above.

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The English version of this fix should have the following file attributes or later:


   Version      File name
   -------------------------

   6.50.427     Opends60.dll

NOTE: With the hotfix installed, error 17802 is now raised within ODS when a thread cannot be created.

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the above files may also contain additional files.

WORKAROUND

Evaluate the system configuration, including the number of worker threads, RA worker threads, and memory settings, and modify the configuration so that the total allocation does not exceed 2 GB.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5.

MORE INFORMATION

On Windows NT, each process has a 4 GB virtual address space, with the lower 2 GB available to the application and the upper 2 GB reserved for Windows NT. All memory allocations made by or on behalf of the application are mapped into this lower 2 GB virtual address space.

Even if a computer has more than 2 GB of memory, an application cannot directly allocate more than 2 GB, because the memory must be mapped into the available 2 GB user address space. (The exception is to use the Virtual*VLM family of APIs, which do not map the memory into the virtual address space of the process.)

On larger systems, the stack space reserved for each thread becomes significant and should be considered when analyzing the maximum amount of memory that should be allocated to SQL Server. By default, the stack size is 1 MB, but it can be altered. For additional information about modifying a thread's stack size, please see the following article in the Microsoft Knowledge Base:

Q160683 INF: How to Use Editbin to Adjust SQL Server Thread Stack Size

As an example, consider a system with the following configuration:

Dedicated SQL Server computer with 2 GB of physical memory

1.5 GB allocated to SQL Server

350 worker threads

255 RA worker threads

When SQL Server starts up, it allocates a single 1.5 GB block of memory (as indicated in the above configuration example). Furthermore, it spawns 510 threads for read ahead, each consuming another 1 MB of stack space. Additional threads are spawned as users connect to the system, also requiring a 1 MB stack as well as other static structures, such as the memory for the PSS structure (around 40 KB) per connection. On such a system, the available 2 GB address space will be entirely consumed as users start connecting to the system, resulting in various failures as described in the SYMPTOMS section of this article.

On systems with less than 2 GB of available memory, the effects of such a poor configuration may not be as obvious because the excessive memory requirements are handled by the virtual memory manager and swapped out to the page file. However, when the allocated memory reaches the 2 GB limit, the virtual memory manager is no longer able to mask the problem, and all subsequent memory allocations will fail, resulting in unpredictable behavior.

Additional query words: hang hangs hung address space machine pagefile

Keywords : kbbug6.50
Version : winnt:6.5
Platform : winnt
Issue type : kbbug