 |
|
SQL Product Highlight
Idera - SQL compliance manager
SQL compliance manager is a comprehensive auditing solution that tells you who did what, when and how on your SQL Servers. SQL compliance manager helps you ensure compliance with regulatory and data security requirements.Learn more!
Change Tracking in SQL Server Full Text Search
|
By: Edgewood Solutions | Read Comments | Print Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com. Related Tips: More |
|
Problem
Creation and maintenance of full-text catalogs and indexes in SQL Server 2000 can be taxing on both the server and the administrator, as well as take a lot of time to populate. This can result in increased deployment times and potentially inaccurate search results. To ease the deployment process and increase the uptime of your application, you can use Change-Tracking population.
Solution
In the traditional methods of full-text catalog population, you can choose a full or incremental population. In full population, the catalog is "built from scratch", building an index entry for each row in the table. In incremental population, a timestamp is used to determine where to start the population. The sysfulltextnotify table holds entries for updated rows and is polled by the MSSearch service. You can handle when this table is polled in one of three ways:
| Method | Description | T-SQL Statement |
| Scheduled | Runs on whatever schedule you choose. Can be run via a SQL Agent job | sp_fulltext_table table_name 'Start_change_tracking' sp_fulltext_table table_name 'update_index' |
| On demand | Runs whenever you run the statements. Changes are stored in the sysfulltextnotify table | sp_fulltext_table table_name 'Start_change_tracking' sp_fulltext_table table_name 'update_index' |
| Background | Changes to the table rows are propogated when they occur | sp_fulltext_table table_name 'Start_change_tracking' sp_fulltext_table table_name 'start_background_updateindex' |
The background population offers a couple of benefits:
- Decreased time in propagating changes to the full-text catalog
- A wider distribution of resource usage, since changes are made when the update occurs instead of having to apply the changes all at once
Once the population method is changed to change tracking, an incremental population begins to make sure the catalog is up to date. The only downside to switching to change tracking population is that entries are no longer written to the Event Log; however, you can devise another method for comparing the entries in the table with the number of rows in the full-text catalog to ensure the process is working for you.
Next Steps
- Review your current method for populating full-text catalogs and the time and resources it takes to perform
- Look at more ways to optimize Full-Text Indexing on MSDN
- Review information on sp_fulltext_catalog
- Read more information at MSSQLTIPS
Last Update: 12/26/2006
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |