Why is CRM not performing full text search after installing Update 0.1–Dynamics CRM 2015 On-premise

Before you continue reading this post, make sure you are aware of the full text search enhancement for quick find that has been introduced with Dynamics CRM 2015 Update 0.1. In case you are not aware of this feature, you can refer to my another blog post – https://debajmecrm.com/optimize-your-global-search-by-enabling-full-text-search-in-dynamics-crm/. This post would give you an idea about how to enable the feature in CRM and the benefits you get by enabling the same.

Now coming back to the topic, say you installed update 0.1 in your Dynamics CRM server. Next day you come to office, open your CRM to see the wonderful search working. You open your accounts grid and then search with a search phase. Alas, you see CRM is not performing the Full Text search as it should be. If you are in this situation don’t be upset. Once you finish reading this blog, you might end up with an optimistic feeling  of making it work.

So before we find out what can be the issue, let us make sure that CRM is not performing a full text search. For this open SQL profiler in your SQL server and then search for accounts with search phrase ‘test’ from your CRM UI. If your CRM would be using full text searches, the query would be something like below. See for the highlighted part

exec sp_executesql N’select
top 251 ( select count(1) from Account where ParentAccountId = "account0".AccountId ) as "HierarchyDataChildCount6dcd9e4c001441b09237d51a34853d55"
, "account0".Name as "name"
, "account0".AccountNumber as "accountnumber"
, "account0".Address1_Composite as "address1_composite"
, "account0".AccountId as "accountid"
, "account0".ParentAccountId as "HierarchyDataParentID6dcd9e4c001441b09237d51a34853d55"
, "account0".Address1_Line1 as "address1_line1"
, "account0".Address1_Line2 as "address1_line2"
, "account0".Address1_Line3 as "address1_line3"
, "account0".Address1_City as "address1_city"
, "account0".Address1_StateOrProvince as "address1_stateorprovince"
, "account0".Address1_PostalCode as "address1_postalcode"
, "account0".Address1_Country as "address1_country"
, "account0".ParentAccountIdName as "parentaccountidname"
, "account0".ParentAccountIdYomiName as "parentaccountidyominame"
from
Account as "account0"
where
[account0].[AccountId] in (
SELECT "account0".[Key] AS [AccountId] FROM ContainsTable([AccountBase], (EMailAddress1, AccountNumber, Name), @EMailAddress10) AS "account0") and (((("account0".StateCode = @StateCode0 and ("account0".AccountNumber not like @AccountNumber0 or "account0".AccountNumber is null) and ("account0".AccountNumber not like @AccountNumber1 or "account0".AccountNumber is null) and "account0".Name is not null)))) order by
"account0".Name asc
, "account0".AccountId asc’,N’@StateCode0 int,@AccountNumber0 nvarchar(200),@AccountNumber1 nvarchar(200),@EMailAddress10 nvarchar(200),@AccountNumber2 nvarchar(200),@Name0 nvarchar(200)’,@StateCode0=0,@AccountNumber0=N’2%’,@AccountNumber1=N’4%’,@EMailAddress10=N'((("test*" OR FORMSOF(FREETEXT, "test"))) OR ("test"))’,@AccountNumber2=N'((("test*" OR FORMSOF(FREETEXT, "test"))) OR ("test"))’,@Name0=N'((("test*" OR
FORMSOF(FREETEXT, "test"))) OR ("test"))’

Check for the FORMSOF method used by CRM for full-text search. If your CRM is not using this then it is not using the full text search.

We are not going to let CRM off just like that. If you are like me and wants to dig a little deep well you are in here. So let’s open your SQL Server here. Open your CRM Organization Database –> Storage –> Full Text Catalogs –> CRMFullTextCatalog.

Double click on this. The tables which are full text indexed will appear on the right hand pane of the popup window.

Here we can see that only two tables have been enabled for full text index. If CRM would have completed the full text search successfully, then you would find multiple tables enabled for full-text search. So the big question here. Would enabling the AccountBase table for full-text indexing manually make CRM to go for full text search? The answer is BIG NO. This is because CRM uses other set of tables to identify whether the full-text search should be used for quick find queries.

So what are the two tables. Let’s see.

The first table which is prime importance here is the EntityIndex table. This is the table where CRM maintains all type of indexes for an entity including the primary key index. Let run the below SQL query.

Select * From EntityIndex Where IndexType = 9 and Name = ‘AccountBase_FullText’.

Running this query on an organization database where Full Text search has been configured successfully would give you a result like the one shown below.

By default, CRM names each full text search in the format <entityschemaname>_FullText

The following are the notable columns for the table EntityIndex

IndexId	Primary key of the table
Name	The name of the index
EntityId	The guid of the entity. you can get the entityid based on the schema name of the entity from the EntityView table
IsClustered	Boolean to indicate if the index is clustered or not
IsUnique	Boolean to indicate if it is Unique Key Index
IndexType	The type of the index. 9 is the index for full text search.

 

Now where does CRM maintain the columns on which full text indexing should be performed? CRM maintains it in another table called IndexAttributes. Based on the IndexId of the EntityIndex table, let’s run a query on the IndexAttributes table.

As you can see from the above screenshot, the query returned 3 rows. The reason is that the quick find view of the customers contains only three columns. Only the columns in the quick find view which are of type text are enabled for full text search. A quick glance on the important fields of this entity.

AttributeId	The guid of the attribute enabled for full text search
IndexId	Reference of the IdexId from the EntityIndex table
IsQuickFindManaged	Boolean to indicate if CRM will perform full text searches for this column.

So now you know fully from where CRM does all it’s magic.

Since you know the in and out of this now, let’s now understand the scenarios when full text search does not work even after enabling it through system settings.

• The value for ‘enablequickfindoptimization’ is set to 0 for your organization. This setting is available in the Organization table of your organization database. You should set it to 1 to make full text searches work for your organization. You can edit the value of this field for your organization using the wonderful tool available in codeplex – https://orgdborgsettings.codeplex.com/
• Make sure your CRM maintenance jobs which contains the Indexing management and Reindex All jobs ran in the last 24 hours. You can check the status of your maintenance jobs using the following tool – https://crmjobeditor.codeplex.com/
• Last but not the least, if your CRM async server and CRM App server are different (which is for majority of the CRM deployments), remember to install Update 0.1 on all CRM app servers as well as CRM Async servers. Although this may sound silly, but recently I got multiple complaints from my customers saying that full text search was working great in the dev. environment (APP +  ASYNC on the same server), but not working in PROD. The reason is the administrator of their company installed the update only in the APP servers and not the async servers.

Hope this gives you an insight of CRM Full text search.

Till then you read one or more of my posts, happy CRM’ing.