SQL Server Performance Forum – Threads Archive

Understanding Incrementals with Full-Text Indexes

I’m still trying to understand the frequency needed for running incremental updates for full-text catalogs. A consultant requested a full population be run every day at 5:00pm and incrementals be run every 3 minutes. My question for you is if I change the incremental frequency to every hour, wouldn’t data changes still be available to the application between incrementals? Isn’t the purpose of the incremental simply to update the full-text index so therefor its benefit is in terms of performance, not data availability. The consultant seams to think any updates made between incrementals will not be available to the application until an incremental is run. Thanks, Dave

Incremental population requests are implemented as full populations if any metadata that affects the full-text index for the table has changed since the last population. This includes altering any column, index, or full-text index definitions. So the data or index that are not affected with this change will still be available during the population, as one of the Best practices it is better to use incremental if the data is volatile in this case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Hi Satya, Your reply indicated the data will still be available during the population. What about between populations/incrementals? It sounds like since a full-text index will not see the data changes until a full population or incremental is run. If that’s the case, any code using the full-text index will not see the data changes. Correct? Dave

Yes the changed data will not be until the population is completed, I might need to check this back at my end (will confirm back if any change). Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Any idea what type of performance hit there will be using WITH CHANGE_TRACKING?

This change is instant change and it is advisable to to change when you are doing many updates/inserts otherwise you don’t need to change… I don’t think there will any peformance issue…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I would expect a performance hit if you are using this as the synchronization slows full-text performance somewhat. If performance is too slow, you can cause a full crawl by setting change tracking off and then resetting it to automatic. May be PERFMON will help you here for relevant counters. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I’ll give it a try. Thanks

I would like to know your feedback as it may help me at this end, as it is different as compared the environments & requirement match we have had. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>