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.