SQL Full text query too slow

Question

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  



  

Answer 1

Hi Amelia,

Thanks for the reply.

The query optimization is always a point in my head.
However, in this case it does not seem to be the only relevant point, because with the same query has completely different performances, before and after the rebuild, after rebuild catalog with exactly the same query (without any optimization change) have an immediate response, and before the rebuild can take several seconds and even minutes.

About Reorganize the full-text catalog by using ALTER FULLTEXT CATALOG REORGANIZE, I already have tried it before, but in some cases didn’t solved the problem, that have been solved just with the rebuild.

Any other suggestions?

Thanks & regards
Rui

Answer 2

Hi @Rui Oliveira ,

Here are some recommendations to help improve full-text query performance:
Defragment the index of the base table by using ALTER INDEX REORGANIZE.
Reorganize the full-text catalog by using ALTER FULLTEXT CATALOG REORGANIZE. You can check the number of fragments in the index by checking the contents of sys.fulltext_index_fragments where the table ID matches the object ID of the table. Make sure that you do this before performance testing because running this statement causes a master merge of the full-text indexes in that catalog.
For more information, please refer to Improve the Performance of Full-Text Queries and this technical article.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet