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