This seems to be another one of those “I used to think..”. In this case, I used to think that, once a column is added to the quick find view “find columns”, that column will be indexed automatically. Well, turned out it’s a bit more complicated; although, in the end, it’s probably still correct.
First of all, it would be worth looking at these two posts:
https://blogs.msdn.microsoft.com/darrenliu/2014/04/02/crm-2013-maintenance-jobs/
There is a bunch of useful information there, but what I was sort of missing is some low-level details. Yes, we can download Job Editor tool from codeplex; however, what is it, actually, going to do? The tool only works in the on-premise environment, and, from what I understand, it goes directly to the MSCRM_CONFIG database to update ScaleGroupOrganizationMaintenanceJobs table:
That table maintains the schedule of all those maintenance jobs per Operation Type per OrganizationId:
It seems that OperationType 15 corresponds to the Indexing Management job (I will explain why that’s important, just keep reading), even though there can be more than one job with that type (one per organization).
That said, here is how I got that index added to the table:
I created a new field on the entity
Then I added that field to the quick find columns
And, of course, published the changes.. on the database side, there was still no index:
So, I downloaded the tool and tried to reschedule the indexing job. That did not quite work out.. The job got rescheduled, but not for the date for which I wanted it (I wanted to see if the index gets created.. so I actually wanted that job to run almost immediately. Instead, it kept moving 1 day forward every time I would try to reschedule it using the tool)
Eventually, I just went to the SQL Management Studio and ran this SQL query:
UPDATE ScaleGroupOrganizationMaintenanceJobs
SET NextRunTime = ‘2017-11-01 02:10:00’
WHERE id=’…’
(If you ever need to run the same query, make sure you change the id and, also, the date.. that date should be in UTC)
The I restarted the Async Services (both, though it may be sufficient to restart the “maintenance” one), and voila.. There is an index now:
Sure you can do this only in the on-premise environments, and only if you have required permissions on the SQL server. However, normally we would not need it. What we should probably keep in mind (as a result of this exercise) is that:
- Yes, indexes are created when a field is added to the “find columns”
- No, they are not created right away – there is a scheduled job that is running daily
- If you are working in the on-premise environment, you may be able to reschedule that job. If you are working in the online environment, you may just have to wait
PS. And, if I had full-text search enabled, this whole story would be different.. but that’s for another post.