We had a client call our Support desk with a curious problem. When they went to their Contact list and typed one particular name in the Quick Find search box, it wouldn’t find the contact record. All other names seemed to work, but whenever they searched for a gentleman with the last name of “Way”, it would not find his record. They could find Mr. Way in advanced find, but never using the Quick Find.
This particular client was using the new(ish) option to “Enable full-text searching for Quick Find”. Disabling this, and waiting the frustratingly long 24 hours for the change to bite, resolved the issue. But users had grown accustomed to the quick response of the Quick Find.
We looked at everything from potentially bad, unprintable characters in the name to T-SQL reserved words to minimum search-string lengths, but couldn’t find the answer. A Microsoft engineer had to finally figure it out for us, so I thought I would write it up.
When you enable full-text searching for Quick Find in CRM 2015 or later, the database engine uses something called a full-text catalog to index the data it may need to search. Search engines, like Google or Bing, use the same notion to be able to search millions of websites in a fraction of a second – they scour websites and pull out important words and store them in a highly-indexed database to provide fast searching when a user uses that word in their search.
However, there are a lot of words and symbols that are just so common and ubiquitous, that they are useless to include as part of a search. “a”, “is”, and “the” are good examples – search engines ignore words like that because they are too common. SQL Server’s Full-text Engine is no different. To prevent a full-text index from becoming bloated, SQL Server has a mechanism that discards commonly occurring strings that do not help the search. These discarded strings are called stopwords . During index creation, the Full-Text Engine omits stopwords from the full-text index. This means that full-text queries will not search on stopwords.
You can find the list of stopwords on your SQL server by using this query:
select * from sys.fulltext_system_stopwords where language_id =1033
(note: if you’re using a language other than English, you may need to change or just omit the language_id part of that where clause)
On my server there are 150+ stopwords, and what do you know – “way” is one of them.
So every time anyone searched for “way” or any other word in this list, SQL doesn’t even try the search because it knows it will not find any results because it wasn’t indexing that word in the first place.
If this problem affects you, you have two choices:
- Elect to not use the Full-text search option in CRM, and revert back to the old method
- If you are on-premise, you can have your DBA update the list of stopwords by creating a custom stoplist.
Pretty good article on stopwords and stoplists here: https://msdn.microsoft.com/en-us/library/ms142551.aspx