Full Text Search Finding Word Only Occasionally

Question

I have a table with a full text index. This query returns 2 results:

SELECT *
FROM SampleTable
WHERE ContentForSearch LIKE '% mount %'
ORDER BY 1

This query returns 1 result:

SELECT *
FROM SampleTable
WHERE CONTAINS(ContentForSearch, '"mount"')
ORDER BY 1

Adding a new instance of the word "mount" to the table does show up in the search. Why?

I've already checked the stopwords list as best as I knew how to. This returns no results:

SELECT *
FROM sys.fulltext_stoplists

This returns no results:

SELECT *
FROM sys.fulltext_system_stopwords
WHERE stopword like '%mount%'

I also checked to see if the index was up to date, this returned the current time (minus a few minutes) and a 0, indicating idle:

SELECT DATEADD(ss, FULLTEXTCATALOGPROPERTY('SampleTableCatalog','PopulateCompletionAge'), '1/1/1990') AS LastPopulated,
    FULLTEXTCATALOGPROPERTY('SampleTableCatalog','PopulateStatus')

I also did some searches in the string that doesn't show up in the CONTAINS result to see if the ASCII values were strange (and can provide queries if needed), but they were exactly the same as the one that did show up.

On one copy of the database, someone ran:

ALTER FULLTEXT INDEX ON SampleTable SET STOPLIST = OFF;
ALTER FULLTEXT INDEX ON SampleTable SET STOPLIST = SYSTEM;

and that seemed to fix it, but I have no idea why, and I'm uncomfortable making changes I don't understand.

UPDATE Stoleg's comments led me to the solution eventually. Full text indexing was somehow turned off on a certain database server. When that database was then restored to another server, those entries that didn't get indexed on the first server were still not indexed even though the new server was properly updating the index. I found this by using Stoleg's queries to check which rows were missing from the index, and then checking the modified date for those rows (which luckily were stored). I noticed the pattern that rows from the dates when the database was on the other server were not in the index. The solution on the problem server was to turn on full text indexing and rebuild the catalogs. As to how the indexing got turned off, I don't understand it myself. The comment from the DBA on how he solved it was "I added full text search as resource to cluster node. "

Answer 1

Well, the obvious question is: Is 'mount' in your stoplist?

Microsoft Configure and Manage Stopwords and Stoplists for Full-Text Search shows you how to query and update your stop words.

You might also want to review the general info on stoplist from Microsoft.

ADDED

Don't take as insult (not that you sounded insulted). Way too many times, people say they have checked something when they only thought they had -- looking at the wrong database, etc. So wanted you to make sure. I interpreted your some of the time as works with like, not with contains, so I thought it was more likely actually the stoplist.

The only other "obvious" solution would be to rebuild the full text index -- with the thought that changing the stoplist has the same effect on the other database. I suppose you could restart the server first too. But, as another mysterious solution, not a first choice.