BUG: Cannot Have More than Eight Full Text Joins and Operations

ID: Q230103

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 55426 (SQLBUG_70)

SYMPTOMS

SQL Server 7.0 allows the use of join operations from materialized tables. When the number of tables used in all Full Text join operations on the SQL Server exceeds eight, SPIDs can timeout and fail to complete the join operation.

The following are examples of errors that may happen.

[spid 13]SQLState: 37000, Native Error: 7619 [Microsoft][ODBC SQL Server Driver][SQL Server]The query timed out.

If you perform an sp_who or select from sysprocesses, the states of the SPIDS will be RUNNABLE, the command is a SELECT, and the wait type is 0x0.

CAUSE

The MSSearch service allows eight concurrent Rowsets to be active at a single time. All other query requests against the same catalog are queued until the results on an active Rowset are processed.

WORKAROUND

Insert the individual Full Text query result sets into a temporary table and perform the join using the temporary table.

For more details, refer to the SQL Server Books Online section pertaining to "Select Into".

STATUS

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

MORE INFORMATION

The following query performs nine materialized join operations from the same Full Text catalog. Because only eight rowsets can be active from MSSearch, the query times out.

In many of these cases, the actual showplan output outlines a hash merge between two or more of the materialized tables, requiring the rowsets to be active.


select iID, tData 
from tblTest as t,
containstable(tblTest, tData, 'formsof(inflectional, cause)') as A,
containstable(tblTest, t2Data, 'formsof(inflectional, cause)') as B,
containstable(tblTest, t2Data, 'formsof(inflectional, cause)') as C,
containstable(tblTest, t2Data, 'formsof(inflectional, cause)') as D,
containstable(tblTest, t2Data, 'formsof(inflectional, cause)') as E,
containstable(tblTest, t2Data, 'formsof(inflectional, cause)') as F,
containstable(tblTest, t2Data, 'formsof(inflectional, cause)') as G,
containstable(tblTest, t2Data, 'formsof(inflectional, cause)') as H
,containstable(tblTest, t2Data, 'formsof(inflectional, cause)') as I
where
A.[KEY] = t.iID 
and B.[KEY] = t.iID
and C.[KEY] = t.iID
and D.[KEY] = t.iID
and E.[KEY] = t.iID
and F.[KEY] = t.iID
and G.[KEY] = t.iID
and H.[KEY] = t.iID
and I.[KEY] = t.iID
go

The query can be simplified to the following:


select iID, tData 
from tblTest as t,
containstable(tblTest, tData, 'formsof(inflectional, cause)') as A,
containstable(tblTest, t2Data, 'formsof(inflectional, cause)') as B,
where
A.[KEY] = t.iID 
and B.[KEY] = t.iID
go

If more than eight SPIDs are attempting to run a query, it is possible to experience the same behavior. If each of the SPIDs is allowed to obtain the first Rowset, assigning the second Rowset cannot happen and the query will eventually timeout.

Additional query words:

Keywords : kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug