Here are fourteen little known tips that you can use to ensure your SQL Server Full-Text Search queries are performing in the most efficient manner possible.

1. Set the virtual memory size to at least 3 times the physical memory installed in the computer, and set the SQL Server "max server memory" server configuration option to half the virtual memory size setting (1.5 times the physical memory).

Because working with full-text search is very resource intensive, you should have enough physical and virtual memory.

2. Set the "Maximize Throughput for Network Applications" option.

This can increase full-text search performance as Windows NT will allocate more RAM to SQL Server than to its file cache.

To set this option, you can do the following:

1. Double-click the Network icon in Control Panel;
2. Click the Services tab;
3. Click Server to select it, and then click the Properties button;
4. Click Maximize Throughput for Network Applications, and then click OK;
5. Restart the computer.

3. Ensure full-text index population takes place during periods of low database access.

Because full-text index population takes some time, these updates should be scheduled during CPU idle time and slow production periods.

4. Assign a very large table (a table that has millions of rows) to its own full-text catalog.

This can improve performance and can be used to simplify administering and monitoring.

5. You can boost the resource usage for the full-text search service (increase the "System Resource Usage" option for the full-text search service).

Run SQL Server Enterprise Manager, expand a server group, and then expand a server. Then expand "Support Services", right-click the "Full-Text Search" and select "Properties". Finally, choose the "Performance" tab and increase the "System Resource Usage" option for the full-text search service.

Note: Don't set the "System Resource Usage" option to the "Dedicated" value (right border of the "System Resource Usage" slider bar) as it can negatively affect your SQL Server's performance.

6. Reduce the full-text unique key size.

To create a full-text index, the table to be indexed must have a unique index. Try to select a numeric column as the full-text unique key to increase the speed of full-text population. If the table to be indexed does not have a numeric unique index, consider creating a numeric unique index.

7. If you have several physical disks, create several Pagefile.sys files so that each Pagefile.sys file will be placed on its own physical disk.

Spreading paging files across multiple disk drives and controllers improves performance on most disk systems because multiple disks can process input/output requests concurrently.

8. If you use SQL Server 2000, consider using the Change Tracking with scheduled or background update index option versus Incremental Population.

The Change Tracking with scheduled propagation should be used when CPU and memory can be used at scheduled times and changes between the scheduled times are not significant.

The Change Tracking with background update index option should be used when CPU and memory are available and the value of an up-to-date index is high.

9. Consider using a full population when a large percentage of records were changed or added at once.

10. If you work with SQL Server 7.0, consider using an incremental population when there have not been a large percentage of records changed or added at once.

Using an incremental population instead of a full population decreases the population time and results in good performance benefits.

11. If you have several physical disks, place the database files separately from the full-text catalog files.

This can produce speed improvements for full-text queries as multiple disks can process input/output requests concurrently.

12. Upgrade to SQL Server 2000 in order to enhance full-text search performance, especially if you need to work with full-text search in clustered environment.

The full text search is not available in SQL Server 7.0 clustered environment.

13. If you work with SQL Server 2000, consider using the new top_n_by_rank parameter with CONTAINSTABLE or FREETEXTTABLE.

It can be used to restrict the number of rows returned. The top_n_by_rank parameter specifies that only the n-highest ranked matches, in descending order, will be returned.

14. Try to use the CONTAINS or FREETEXT predicates instead of the CONTAINSTABLE or FREETEXTTABLE rowset functions, whenever possible.

Because qualifying rows returned by the CONTAINSTABLE or FREETEXTTABLE rowset functions must be explicitly joined with the rows in the original SQL Server table, the queries that use the CONTAINSTABLE and FREETEXTTABLE functions are more complex than those that use the CONTAINS and FREETEXT predicates.

» See All Articles by Columnist Alexander Chigrik