I've got a
FTS Catalog on a table containing ~106 row. It used to work like a charm, but lately, for an unknown reason it started to get very bad (query >30 sec) performances randomly.
Reading Guidelines for full-text index maintenance I dig around
sys.fulltext_index_fragments and noticed the following:
- The number of fragment oscillate between 2 and 20 at high frequency (multiple times per minutes);
- The biggest fragment contains ~105 rows and is 11Mb;
- The others contains from 1 to 40 rows.
Can this oscillating number of fragment mess up SQL Server's execution plan selection?
What can I do to clean up this?