BUG: SP2 Regr:Many-Column SELECT, JOINs, ORDER BY May Disconnect

ID: Q195536

The information in this article applies to:

Microsoft SQL Server version 6.5 Service Pack 2 and later

BUG #: 18406 (SQLBUG_65)

SYMPTOMS

A query may disconnect the client process from the server if all of the following conditions are true:

The query returns many columns (having max rowsize over 2014).

There is more than one table involved in the query.

The query includes an ORDER BY clause.

When executed from within ISQL/W, the query may cause the ISQL/W tool to stop responding, forcing you to use the Windows NT Task Manager to close it. From within command line ISQL, the query will briefly stop the process then disconnect it.

In both instances, a handled exception access violation (AV) stack will be written to the SQL Server error log.

WORKAROUND

To work around this problem, do any one of the following:

Remove some of the columns.

-or-

Alter the query, using UNION clauses or temporary work tables.

-or-

For reporting sake, consider combining multiple result sets horizontally in the client-side (consequently, returning narrower result sets with each query).

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5 Service Pack 2 and later.

MORE INFORMATION

This problem appears to be related to the issue described in the following article in the Microsoft Knowledge Base:

Q181923 : SELECT with Many Columns Causes Handled AV in Optimizer

But the stack trace generated is different, and in this instance there will be no mention of EXCEPTION_INT_DIVIDE_BY_ZERO, as in that case.

However, in light of the issue described in that article, it would be best to keep the total number of bytes-per-row returned less than 2,000.

Additional query words: AV maximum freeze hang hangs hung errorlog err
worktable worktables


Keywords          : SSrvTran_SQL kbbug6.50.sp2 kbbug6.50.sp3 kbbug6.50.sp4 
Version           : WINNT:6.5 SP2
Platform          : 
Issue type        : kbbug

Last Reviewed: April 21, 1999