BUG: Heavy Full Text Query Activity Results in Unexpected Timeout Errors

ID: Q230036

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 55395 (SQLBUG_70)

SYMPTOMS

Heavy Full Text Search activity from SQL Server against a single catalog can result in timeout errors or slower than normal full text query completion.

The following error may occur:

Server: Msg 7619, Level 16, State 1, Line 3
The query timed out.

A time out occurred while waiting for memory resources to execute the query. Rerun the query.

The SPIDs will show up in the sp_who or sysprocesses output as RUNNING with a wait type of 0x0.

When this activity occurs other Transact-SQL commands will execute properly.

WORKAROUND

Following is a list of suggested workarounds. These may need to be combined to achieve the desired results.

Change the Full Text Select statement to insert into a temporary table instead of streaming the results directly to the client.

For more details please refer to the "Select Into" documentation in SQL Server 7.0 Books Online.

Adjust the sp_configure setting 'remote query timeout(s)'. If the value is not zero (0), it is passed to the Full Text Search query to control the timeout value. If the value remains zero (0) the default timeout of 5 minutes is used.

Reduce the size of the result set. This increases Rowset processing and reduces network traffic.

STATUS

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

MORE INFORMATION

The MSSearch service supports the Full Text catalogs of SQL Server. By default the MSSearch service is limited to eight active rowsets per catalog. When more than eight Full Text queries are queued the MSSearch service queues the ninth, tenth and subsequent requests.

If SQL Server is busy returning results from the first eight requests all other requests can be queued, leading to unexpected timeouts or slower than expected query execution.

By implementing the workaround the results are retrieved from the MSSearch service without network latency, increasing search performance in many instances.

The MSSearch Service provides a rich set of performance counters to monitor the service activity. Counters like "Active Connections" or "Byte Sent Rate" can be helpful when tracking the work load.

Additional query words:


Keywords          : kbSQLServ700 kbSQLServ700bug 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: May 10, 1999