Update queries slower after enabling SQL Server Full-Text Index

Question

I have an asp.net website with many insert, update, and delete queries running against my database.

A few days ago I create a Full-Text Index on two columns of one of tables. After that I realized that when the website runs update queries on that table, memory and disk usage of the SQL Server Process is jumping and updates are slower. The queries ran without any performance issues before creating the Full-Text index.

I have also realized that update queries that were very simple before, are now complicated, since the execution plan now has things such as Full Text index update. This is a part of a new execution plan that became complicated after enabling Full-Text:

In some hours when I update site contents, I ran 5000 update queries and I think full text indexing process is done each time for each row.

Should I disable full text scanning on start of updating rows and then re enable it (as in this related question)?

Can I tell SQL Server to stop full text indexing for 5 minutes and then start indexing new data?

Is there any better alternative? I am using SQL Server 2012.

Answer 1

In my case I had to change an inefficient UPDATE statement:

Before:

UPDATE Customer 
SET Rating = 'Not Rated'
WHERE ...

After:

UPDATE Customer
SET Rating = 'Not Rated'
WHERE ...
AND Rating <> 'Not Rated' -- THIS LINE WAS INSERTED

This improved performance from 4 minutes down to 5 seconds. The problem was that I used to update all rows, even if they already had the value that I wanted to assign.

Answer 2

If you stop it (disable it), the index won't be available for queries using it.

However, you can set change tracking to manual:

--disable automatic change tracking
ALTER FULLTEXT INDEX ON schema.table SET CHANGE_TRACKING MANUAL
GO

--run the update statement here

--re-enable automatical change tracking after updating it to reflect the recent changes
ALTER FULLTEXT INDEX schema.table START UPDATE POPULATION
GO
ALTER FULLTEXT INDEX ON schema.table SET CHANGE_TRACKING AUTO
GO

You can check the current value with this query:

SELECT TOP(10) change_tracking_state_desc, * 
FROM sys.fulltext_indexes 
WHERE object_name(object_id) = '...';

Valid options are: SET CHANGE_TRACKING {MANUAL | AUTO | OFF}

Specifies whether changes (updates, deletes, or inserts) made to table columns that are covered by the full-text index will be propagated by SQL Server to the full-text index. Data changes through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking.

See the Microsoft documentation for ALTER FULLTEXT INDEX (Transact-SQL).