Microsoft KB Archive/303459

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

Article ID: 303459

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



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:

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.

