Can oscillating number of fragment decrease FTS query performance? 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?
Fragmentation can cause query performance issues if there is a significant number of them. I've generally seen it with fragments counts greater than 50 to 100 fragments for the specific full-text index. Not generally noticeable with 20 or less as you describe. It also sounds like master merge is doing its job if you see the number of fragments changing from 2 to 20 and then back down again. This would indicate that as population runs and increases the fragments that the master merge process is then reducing the number of fragments.

Execution plans will not change as a result of fragmentation because Full-Text is implemented via a TVF. So all you ever see in the execution plan is a TVF no matter how many fragments there are. If you are seeing execution plan changes, it is likely due to other reasons than fragmentation. Regrettably, there is currently no way to see what queries actually are in the TVF to resolve your full-text search request. The more complicated your search request, the more work full-text may need to do.

If you are experiencing plan changes and believe that full-text is the reason for the plan changes, it is more likely due to the estimated/actual number of rows the TVF is expected to return. Another thing that could affect performance is what point the TVF is joined to the table and whether or not the plan processed the TVF first or the table first. Search conditions for full-text obviously affect how many rows are returned so the one question I would have is do you get the same performance repeatedly for the same exact search term? Does the performance change as search terms are changed? Is there any wait stats for the query or is the query getting blocked? All these things may help further understand things.

More work is needed to fully understand why you are getting variable performance with full-text.

MORE INFORMATION
Fragments are created during population. This helps with speeding up the population process. Internally, each fragment is a separate table, so think of querying a lot of fragments as UNION of all the fragment tables. As you can imagine, the fewer of them the better the performance.

Many users of full-text confuse the rebuilding of a regular index and think it is also necessary to "rebuild" the full-text index, but actually the opposite is true.

A rebuild of a full-text catalog causes fragmentation of the index(es) because a rebuild is doing a full population and population creates fragments to speed up population. You should only ever rebuild a full-text index if it is corrupted or you change word breakers, ifilters, stop lists, - essentially anything that would change what is stored in the index.

At the end of a full or incremental population, the master merge process (what reduces fragments) automatically runs. However, there is a limit on the number of simultaneous merge processes that can be run at the same time. This is especially important to understand because if you have a lot of full-text indexes on the SQL Instance or you put several full-text indexes into the same full-text catalog and you either rebuild OR reorganize AND the population of several indexes finish near or at the same time it can exceed the threshold. This can result in the fragments for those merge process that exceed the threshold to go into a wait state and the end result is that the fragments for the affected full-text indexes is not reduced. See full-text population slow

Hope this helps.