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/2015/11/10/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.

image

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

image

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.

image

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.

image

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.

Advertisements

Author: Debajit

I am a Dynamics CRM Most Valuable Professional (MVP) with 10 years of experience in Microsoft .NET Technologies and 7 years of dedicated experience in Microsoft Dynamics CRM. I have worked with companies like Microsoft, SanDisk, PwC, TMF Group and have extensive experience of implementing complex CRM solutions from both offshore and client side. Currently the face of XrmForYou.com with significant experience in delivering corporate training on Dynamics CRM and have already delivered multiple projects to client through XrmForYou.com Author of multiple tools on codeplex including the 'Role Based Views' and 'CRM-Sharepoint Metadata manager & Attachment Extractor' which are available for commercial use under XrmForYou.com For consulting/ training, drop me a note at info@xrmforyou.com or visit our website www.xrmforyou.com

2 thoughts on “Why is CRM not performing full text search after installing Update 0.1–Dynamics CRM 2015 On-premise”

    1. Hi Rahul,
      Thanks for reading my post.
      for that matter, this post is all about going under the hood and understand the details. In fact nothing unsupported is suggested and done in this post.

      The final three troubleshooting areas that I provided if full text search does not work, are all supported.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s