Full text rebuild option – and SQL Server performance

03 September,2013 by Jack Vamvas

Why use the Full-text upgrade option Server property?And  how to plan for performance impact of full-text rebuild?

 The SQL Server property Full-text index upgrade option defines how the full-text indexes are migrated during an upgrade for the following circumstances

1)     Database attach

2)     Restore from a database backup

3)     Restoring from a file backup

4)     Using the Copy Database wizard

The main benefit of enabling the Full-text upgrade option is to automatically upgrade the database full-text indexes. The action depends on the Full-text upgrade option – choices are : a) Imports b) Resets c) Rebuild. For greater detail read SQL Server BOL.

 Notes on full-text upgrade option usage

1)   If using Import or rebuild , the full-text indexes are unavailable during the upgrade option

2)     When importing large databases , with large indexing requirements the import duration can be significant. As noted earlier, the indexes are unavailable during the import process

3)     When rebuilding large indexes , the duration can be long. Factor in index unavailability during the rebuild. Consider completing the Index Rebuild stage outside of business hours.

4)     During the index rebuild stage, as well as the unavailability, large amounts of resources are used. The DBA will notice large amounts of CPU and Memory consumed. This can have a negative impact on other services sharing the same hardware. Even if the Full-text rebuild is occurring on a separate VM , a shared underlying hardware can experience severe bottlenecks impacting other VMs on the utilising the same hardware.   Factor into planning for Virtualization – and database servers

Read More

SQL Full Text rebuild - SQL Server DBA

10 Ways to Optimize SQL Server Full-text Indexing

SQL Server Full Text Search Optimization using CONTAINSTABLE

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Full text rebuild option – and SQL Server performance

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer