June 20, 2014 at 10:37 am
One of the Devs reported that when doing a Full Text search today for an exact phrase (that he *knows* exists,) nothing was returned.
He did a rebuild of the catalog, re-ran the query, and found the phrase. No errors, nothing.
The catalog is configured for Automatic change tracking, but we don't do scheduled rebuilds / optimizations of the catalog. Should we be doing such things?
As of right now, I've got an Agent job setup to rebuild the catalog nightly (except for weekends) as that resolved the problem the first time. But, any insight into what might cause such behavior would be appreciated as well...
Thanks,
Jason
June 20, 2014 at 6:08 pm
jasona.work (6/20/2014)
The catalog is configured for Automatic change tracking, but we don't do scheduled rebuilds / optimizations of the catalog. Should we be doing such things?
Yes.
As of right now, I've got an Agent job setup to rebuild the catalog nightly (except for weekends) as that resolved the problem the first time. But, any insight into what might cause such behavior would be appreciated as well...
Thanks,
Jason
From http://msdn.microsoft.com/en-us/library/ms142575.aspx
Full Population
During a full population, index entries are built for all the rows of a table or indexed view. A full population of a full-text index, builds index entries for all the rows of the base table or indexed view.
By default, SQL Server populates a new full-text index fully as soon as it is created. However, a full population can consume a significant amount of resources.
Further on, in Change Tracking:
Change Tracking-Based Population
Optionally, you can use change tracking to maintain a full-text index after its initial full population.
...
•Automatic population
By default, or if you specify CHANGE_TRACKING AUTO, the Full-Text Engine uses automatic population on the full-text index. After the initial full population completes, changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index.
That's assuming it's on, otherwise you have this:
Manual population
If you specify CHANGE_TRACKING MANUAL, the Full-Text Engine uses manual population on the full-text index. After the initial full population completes, changes are tracked as data is modified in the base table. However, they are not propagated to the full-text index until you execute an ALTER FULLTEXT INDEX … START UPDATE POPULATION statement.
So, generally either the change tracking is seriously delayed, or you're on a manual change tracking scenario.
The page I linked is for 2014, but 2k8R2 acts nearly exactly the same. I usually schedule a population update nightly for any Full Text Indexes I build, or at least a full pop rebuild on the weekends. I've found it seems to... miss things up to 2k8 otherwise. I haven't had a chance to play in '12/'14, so I can't speak to those.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply