Full-Text Search Optimization Tips
- 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 expensive,
you should have enough physical and virtual memory.
- Set the "Maximize Throughput for Network Applications" option.
This can increase full-text search performance, because 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.
- Make full-text index population 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.
- 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
- You can boost the resource usage for the full-text search service
(increase the "System Resource Usage" option for the full-text
Run SQL Server Enterprise Manager, expand a server group then expand
a server. Expand "Support Services", then right-click the "Full-Text
Search" and select "Properties". Choose the "Performance" tab and
increase the "System Resource Usage" option for the full-text search
Note. Don't set the "System Resource Usage" option to the "Dedicated"
value (right border of the "System Resource Usage" slider bar),
because it can negatively affect your SQL Server's performance.
- 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 numeric unique index,
consider creating numeric unique index.
- If you have several physical disks, create several Pagefile.sys
files, so that each Pagefile.sys file will be placed on its own
Spreading paging files across multiple disk drives and controllers
improves performance on most disk systems because multiple disks
can process input/output requests concurrently.
- If you use SQL Server 2000, consider using the Change Tracking
with scheduled or background update index option versus Incremental
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.
- Consider using a full population when a large percentage of
records were changed or added at once.
- If you work with SQL Server 7.0, consider using an incremental
population when not a large percentage of records were changed or
added at once.
Using an incremental population instead of a full population decreases
the population time and results in good performance benefits.
- If you have several physical disks, place the database files
separately from the full-text catalog files.
So, you can improve the speed of full-text queries, because multiple
disks can process input/output requests concurrently.
- Upgrade to SQL Server 2000, in order to enhance full-text search
performance and if you need to work with full-text search in
The full text search is not available in SQL Server 7.0 clustered
- 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.
- Try to use the CONTAINS or FREETEXT predicates instead of
the CONTAINSTABLE or FREETEXTTABLE functions to simplify
the query's text.
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