How to use a custom stoplist for Fulltext search in D365 onprem

Have you activated the “Fulltext Search” functionality in your D365 onprem environment?

2018-02-08 17_32_10-CRM Q Frontend 1

The fulltext search is quite powerful but did you know that the fulltext search is using a “stoplist” in SQL server which can impact your search results?

You will ask now: What is a stoplist and what is it used for?

Microsoft explains here
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.

Let me explain the behavior using an example:
Let’s say we go to the account entity in D365 and search for a company called “STILL”. The SQL query performed by the SQL server considers the fulltext catalog which was built after enabling the fulltext search. For filling the fulltext catalog a stoplist is considered. That means the Full-Text Engine omits stopwords from the full-text catalog.
From the time we enabled the fulltext search in D365 the SQL server system toplist is used for filling the catalog. The system stoplist contained in our case more than 15.000 entries for different languages. One of the entries was “Still” for several languages. As result the account I was trying to find using the searchterm “Still” was not found.

What needs to be done?
1. Open SQL Server Management Studio and create a new stoplist for your D365 database:
2018-02-08 17_57_59-CRM Dev

2018-02-08 18_02_22-CRM Dev

2. Remove the undesired stopword from your stoplist:

    ALTER FULLTEXT STOPLIST [CRM_stoplist] drop 'still' LANGUAGE 'English';

    Attention: The fulltext search will only consider the base language of the SQL server installation. That means we cannot use different stopwords for different D365 languages. Independent of the users language the fulltext search will always be performed using the stopwords in the base language of the SQL server installation.

3. Recreate the fulltext index which is used by the fulltext search

    DROP FULLTEXT INDEX ON accountbase

    CREATE FULLTEXT INDEX ON dbo.accountbase(accountnumber, name, orb_erpaccountnumber, orb_name4, telephone1)
    key index cndx_primarykey_account
    with STOPLIST = CRM_stoplist

4. Execute the following queries one by one

    -- Please execute the command below to disable the "CHANGE_TRACKING" parameter before performing the FULL POPULATION:
    ALTER FULLTEXT INDEX ON dbo.accountbaseSET CHANGE_TRACKING MANUAL;

    -- Then at an OFF-PRODUCTION time, perform the FULL POPULATION:
    ALTER FULLTEXT INDEX ON dbo.accountbaseSTART FULL POPULATION;

    -- As this command can take a long time to process, you can check whether the process has finished or not by executing the query below:
    select * from sys.dm_fts_index_population
    where table_id = object_id('accountbase')
    and population_type_description = 'FULL'
    -- when you find a record the process is still running

    -- After the process is finished, enable the "CHANGE_TRACKING" parameter again:
    ALTER FULLTEXT INDEX ON dbo.accountbaseSET CHANGE_TRACKING AUTO;

Now we are able to find the desired account using the account quickfind with the searchterm “Still”.

Summary:
Be aware of “strange” search results when enabling Fulltext Search in D365 onpremise environments. Create your own stoplist and clean it up from stopwords impacting your search results.

No comments yet.

Leave a Reply