Microsoft KB Archive/230103

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

Article ID: 230103

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q230103



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 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: FTS full text search full-text

Keywords: kbbug kbpending KB230103

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.