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

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

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.

• 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.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base: For more information about how to improve query performance for the full-text search on very large catalogs in Microsoft SQL Server 2005, visit the following Microsoft Web site: