INF: How to Improve the Performance of Full-Text Search Queries for Large Database Tables (303459)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0
This article was previously published under Q303459
IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

SUMMARY

This article provides information about how to improve the performance of full-text search queries on large database tables in Microsoft SQL Server.

MORE INFORMATION

You may experience performance issues with full-text search queries for large database tables that have either:
  • A large number of rows that contain millions of records.

-or-

  • A large number of unique full-text index words.


This problem occurs frequently if the total size of all the catalog files exceeds 256 megabytes (MB).

If you experience performance issues with a full-text index, you must change the MaxPropStoreCachedSize Registry key value in the Windows Registry. The Registry key path is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer

The REG_DWORD of the MaxPropStoreCachedSize key controls the memory, in megabytes (MB), that a catalog caches. The value of the MaxPropStoreCachedSize key is the maximum memory that each search instance can allocate.

The MaxPropStoreCachedSize value must be five percent more than the total size of all the catalog files. The size of the catalog files is the sum of the sizes of the category file extensions (*.ps1 and *.ps2). Adjust the MaxPropStoreCachedSize value accordingly to assure an optimum use of virtual address space. You must be cautious when you adjust the MaxPropStoreCachedSize value because a reduction in the cache size may affect the performance.

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

298794 PRB: Population or Query of a Full Text Catalog May Stop Responding


240833 FIX: Full-Text Search Support for TOP via CONTAINSTABLE and FREETEXTTABLE Clauses

Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbRegistry kbinfo KB303459 kbAudDeveloper
©2003 Microsoft Corporation. All rights reserved.