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:

  1. The number of fragment oscillate between 2 and 20 at high frequency (multiple times per minutes);
  2. The biggest fragment contains ~105 rows and is 11Mb;
  3. 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?

  • Is it possible to add the query to your question and/or the query plan to pastetheplan? The table definition could help too. Did you try ALTER FULLTEXT CATALOG ftCatalog REORGANIZE; as mentioned in the docs on sys.fulltext_index_fragments? – Randi Vertongen Jul 23 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.