This article is intended for users that are using SQL server full text indexing feature and want to know what are the best practices using full text indexes and how we can troubleshoot any problems with it.

What is Full text Index?

A full-text index is a special type of index that provides index access for full-text queries against character or binary column data. If we want database engine to search within content of the document then we must create full text catalog.

Once full text index is created on a table and a column you can search within that column content using Contains keyword.

Common problems with FTS

Your table with FT index can grow huge and with that even your full text index as well. Hence, it is immensely important to follow best practices otherwise you can run into serious challenges.

With growth in your FT catalog there could be issues like

1. Fragmentation
2. FT stops getting populated

Best Practices 

So you may ask what should be the default practice to manage my full text catalog automatically ?

Well simple and most effective solution is to schedule a reorganize index option every weekend.

According to Microsoft’s documentation it will merge all the fragments created over time for each Full-Text index to a single “container” (but it’s also a fragment). This operation is called a master merge. It’s represented in the figure below:

while we can schedule reorganize index as a SQL job from one of the procedure potential problems which could occur with this approach are:

• Blocking or deadlocks.
• Transaction Log full error

However, in our clients case there was the second problem (i.e. the FT stops getting populated ) happened.

we recognized this by adding a new record to the same table and found that FT was not working and unable to search content for newly added doc.

Hence, we had to follow the second option which is rebuilt index.

we schedule Rebuilding entire index again. problems with this approach are the following :

• The application will fail to query these indexes during the operation.
• It will take resources to read the entire table in order to create the index
• If there are unexpected cases faced by the solution, the CREATE statement might fail

we ran this entire rebuild index process for almost 3.5 days most of it was the weekend and finally after all this our indexes were back and working fine. even after adding a new row system was able to search from the content.

Feel free to reach me out for any further queries or questions in reference to this article or anything else, I could be reached at maulik@envitics.com or you can send your questions here https://envitics.com/contact