Sql-server – Update queries slower after enabling SQL Server Full-Text Index

full-text-searchperformancequery-performancesql serversql-server-2012

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:

enter image description here

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.

Best Answer

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.

Related Question