I have some Full Text Catalogs and since some time ago, the query time really slow down, and the only solution I found until now is to rebuild the catalogs, sometimes more than once a day with command:
ALTER FULLTEXT CATALOG TABLE1 REBUILD
Why is necessary to rebuild the catalogs? There are other solutions for this problem?
After run the Rebuild command the query is instantaneous and when slow down could take several seconds or a minute.
My Catalogs have been created by this code:
DECLARE @catalog VARCHAR(255)
DECLARE @pkCatalog VARCHAR(255)
SET @catalog = 'TABLE1'
SET @pkCatalog = 'PK_' + @catalog
EXEC sp_fulltext_database 'enable'
EXEC sp_fulltext_catalog @catalog, 'create'
EXEC sp_fulltext_table @catalog, 'create', @catalog, @pkCatalog
----------------------------------- INICIO CURSOR: cursorColumn
DECLARE cursorColumn CURSOR
FOR
select name from syscolumns where id=object_id(@catalog) and xtype = 167
FOR READ ONLY
OPEN cursorColumn
DECLARE @colName SYSNAME
FETCH NEXT FROM cursorColumn INTO @colName
WHILE (@@fetch_status <> -1)
BEGIN
EXEC sp_fulltext_column @catalog,@colName,'add'
FETCH NEXT FROM cursorColumn INTO @colName
END
CLOSE cursorColumn
DEALLOCATE cursorColumn
------------------------------------- FIM CURSOR: cursorColumn
EXEC sp_fulltext_table @catalog,'activate'
EXEC sp_fulltext_table @catalog, 'Start_change_tracking'
EXEC sp_fulltext_table @catalog, 'Start_background_updateindex'
GO