Optimize your global search by enabling full text search in Dynamics CRM

Are you using CRM 2015 and you have complaints regarding the global search 2015 performance? Well then you are right place.

with CRM 2015 Update 1, Microsoft provides a feature to enable full text search for your organization. Once you download and install Update Rollup 1 for Dynamics 2015, this option would be available to you.

Go to Settings –> System Settings.

In the Setup Quick Find Settings, you would see a new option to enable full-text search

image

You would need to enable it.

Don’t expect to see magic immediately. It may take up to 24 hours for full text search since maintenance job has to be run on the server and make adjustments in the specified CRM Organization. Let’s get a bit under the hood here.

When you enable full text search from your CRM, internally CRM uses the full text catalog. If you open SQL server and navigate to your CRM Organization database –> Storage –> Full text catalogs, you would find the entities mapped for full search. Please note that as I said before, this takes considerable amount of time, hence if you immediately move to your database after enabling the setting in CRM, you might not find the table mappings.

image

 

All this about technical stuff but what about end-user experience?

Well the following are the changes

  • The first change that the user would notice is that they do not need to use wildcards anymore. For example – when before full text search is enable, if you search for the word test and if I take account entity, it would return all account whose any quick find column value begins with Test. However after you enable full text search, users no need to use wild card search. Search by any word and it would evaluate with contains operator.
  • Full Text search enables some data science on your data. It actually returns forms of a word.
    • Incorporates Pluralization (“child”, “children”, etc)
    • Incorporates Tense (“drive”, “drove”, “Driven” etc)
  • Now coming to performance – When I tested this in one of my customer environments, I was simply amazed and impressed by the performance. Previously when the search phrase – *Test returned results in 15 seconds. After enabling Full Text search, the query returns records in less than 2 seconds.

 

Before I close this out, this works only on string field. Optionset fields and likewise would not be incorporated in full-text search.

 

Hope this helps!

Advertisements

Read Committed Snapshot Isolation (RCSI)–Know before you use it for your Dynamics CRM Database

‘Read Committed Snapshot Isolation’ Or RCSI is short is something I continuously keep hearing in my CRM implementations every time there is any discussion related to the CRM performance.  On the lighter side of it, the term itself is very catchy isn’t it.

Since it’s not a database related blog, I will keep the concepts very simple here and focus more on the Dynamics CRM performance part of it. Typically from SQL sense, an isolation level is the degree to which one transaction must be isolated from resource or data modifications made by other transactions. The isolation level under which a Transact-SQL statement executes determines its locking and row versioning behavior.

By default the isolation level for your CRM Organization connection is ‘Read Committed’. You can check by running the below command. You would get a result similar to the below.

DBCC UserOptions

image

In ‘read committed’ isolation, your SQL statement views the most-recently committed data as of the moment each item is physically read. To put it simple, for read committed isolation, each row is locked briefly and physically read.

RCSI improves on this by removing row locking part totally while reading of rows. It provides transaction with the point-in-time view of the committed data, where the point-in-time is the time when the transaction starts executing. When this isolation level is maintained, SQL server maintains row versioning and during read no shared locks are acquired physically because the entire transaction reads from the row version store rather than being accessed directly.

To put in the words of MSDN – “Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server”

From the explanation above, it make obvious sense that it would result in increase in performance. And normally whenever there is any performance discussion on CRM, the question that I come across is – ‘we are thinking of implementing RCSI and it should increase performance. What’s your thoughts?’

Before we implement anything, we should also learn the caveats of it and personally I know many customers who have suffered (transactions rolled back and other stuffs). While this makes an attractive proposition for implementing, let’s see what can be the downside to it.

  • RCSI leverages the tempdb database to store a copy of the original row and adds a transaction sequence number to the  row. So it is important that the physical environment is configured to cope with this, primarily in terms of tempdb performance and memory/disk space requirements.
  • Many of CRM customers have complained that updates are rolled back frequently after enabling RCSI. This is because snapshot isolation uses an optimistic concurrency model. If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised.
  • When snapshot is enabled, there are no shared locks. Any statement running with snapshot isolation cannot see any committed database modifications that occur after the statement starts executing. The longer the statement runs for, the more out-of-date its view of the database becomes, and the greater the scope for possibly-unintended consequences.

For e.g – You have a long running query which determines whether an email should be sent at certain step of the query, based on some attribute value of an entity. Since this is running in snapshot, the transaction at the beginning has taken a copy of the data for the transaction in the tempdb database. So when the email sending condition is evaluated, the value of the determining field might have changed in the database. But since the query is working on the snapshot of data some time back (might be few seconds), it would still evaluate the e-mail sending condition to true.

This is off-course an example. However I hope you guess what analogy I am trying to draw here.

So before you implement, just think what is the concurrency level for your CRM usage and then discuss with your DBA before reaching a decision.

Finally if you go ahead and enable the RCSI, run the below commands.

ALTER DATABASE <Your crm database>
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE <Your Crm Database>
SET READ_COMMITTED_SNAPSHOT ON
Hope this helps!

{Utility}–Access team template migrator for Microsoft Dynamics

Access Teams! Great feature that came with Microsoft Dynamics CRM 2013 version. However after repeated implementations, despite the benefits that access templates provide, one common complain that I have heard

“Why are access teams not solution aware?”

Well lets get a bit deeper here. It’s not the access teams which is loosely mentioned in the above question, that is our problem. It is the access team template and the access team grid configurations on the entity forms which needs to be repeated across environments. And if you have more than a few of them to handle, it can become annoying.

Well, in case you are in the same boat as me, the following utility might come as a sight of relief to you. I have uploaded it in codeplex and it’s free to use.

https://accessteamsmigrator.codeplex.com/releases/view/618241

Please read the detailed documentation on how to use this tool to your advantage – https://accessteamsmigrator.codeplex.com/documentation

This is a beta version. So in case you have any issues with this tool, please drop an email to debajit.prod@gmail.com and I will get back you.

Hope this helps!

image_thumb3_thumb