FIX: Very Large Numbers of Predicates AND-ed Together May Cause Stack Overflow

• Microsoft SQL Server version 7.0

SYMPTOMS

A query that contains a large number of predicates that are AND-ed together may cause a stack overflow exception. Several thousand conditions are typically required for this problem to occur, and you may reach a limit on the maximum batch size of your query tool before encountering this situation. Many query tools limit batches to 128 KB, whereas SQL Server 7.0 now accepts a batch size as large as (65536 * network packet size).

CAUSE

During optimization, memory for selectivity calculation was being allocated from the stack. This allocation depends on the number of nodes being AND-ed together and can result in a stack overflow.

WORKAROUND

To work around this problem, rewrite the query to reduce the number of AND conditions required. For example, you may be able to rewrite the query as a join.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 7.0. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

MORE INFORMATION

The SQL Server error log will contain an entry similar to the following when the exception occurs:

1999-04-01 16:22:13.78 spid7    04/01/99 16:22:13 Stack Overflow Dump not possible - Exception c00000fd E at 780348a4
1999-04-01 16:22:13.78 spid7    Address=780348a4 Exception Code = c00000fd
1999-04-01 16:22:13.78 spid7    eax=00000008 ebx=000d6558 ecx=00000008 edx=7804093a
1999-04-01 16:22:13.78 spid7    esi=00000001 edi=00000000 eip=780348a4 esp=02f82f38
1999-04-01 16:22:13.78 spid7    ebp=02f83208 efl=00010203
1999-04-01 16:22:13.78 spid7    cs=1b ss=23 ds=23 es=23 fs=3b gs=0
1999-04-01 16:22:13.78 spid7    Input Buffer 393228 bytes -
...<query text>...
1999-04-01 16:22:31.64 spid7    Error: 0, Severity: 19, State: 0
1999-04-01 16:22:31.64 spid7    language_exec: Process 7 generated an access violation. SQL Server is terminating this process. 

Additional query words: EXCEPTION_STACK_OVERFLOW language_exec ANDed errorlog error err message msg

Keywords : SSrvErr_Log SSrvGPF SSrvTran_SQL kbbug7.00
Version : winnt:7.0
Platform : winnt
Issue type : kbbug