Full-Text Search Optimization Tips


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

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Latest Articles