There are 2 ways of tracking emails in Dynamics 365: folder-level tracking and correlation method. Folder-level tracking will fit only a few cases, but the correlation method will do the job for most cases and it’s a way to go for most customers.
When you decide to go correlation route there are also 2 (or even 0) ways to do that. First one is to use tracking token which is the most precise method, but also the least accepted method by customers because it adds a token in the subject of the mail. The second one is the smart matching method that is suggested by most people because it gives the flexibility to match emails that are started from the new thread, but is it really worth it?
What is smart matching?
When you search for definition in docs about the smart matching it says that it’s done by checking the email subject, sender and recipients to find the matching email thread in the emails that are synced to Dynamics.
Subject matching is done by setting the regex expression that will remove the all unnecessary characters from the subject to improve the matching algorithm.
Sender and recipient matching allows us to set parameters for maximum numbers of recipients to get better results allowing us to tweak the matching to match our specific needs. In most cases it takes some time until you find the best batch for your specific case.
Sounds like a perfect solution, does it? There is always a but!
Problematic case
I had a problem with some emails that were not synced in Dynamics for a long period of time. Since then I tried to find some patterns between unsynced emails, but with no luck.
Problematic emails were automatic emails sent every now and then with the same subject and to nearly the same recipients by the same sender. Half of the emails are synced to Dynamics and the other half ends in the error. The error message on mailbox just doesn’t tell much about the actual issue.
Emails that were synced successfully looked just like the ones that didn’t when you are looking at the metadata of the email message, so there should be no reason for sync to fail.
Investigation
Investigation started with EWS Editor for Dynamics 365 tool which is used to troubleshoot issues with Exchange server integrations with Dynamics 365. With this tool you can view all the metadata of the particular email and test if that mail can be synced to Dynamics. We had no luck with the tool in this case, but maybe it can point you in right direction while you try to troubleshoot the issues.
After a deep dive into the problem one strange pattern emerged.
Emails with the exact same subject and recipients started to fail after the initial period of successful syncs and after that, they fail to sync every single time. Since it was on the on-premise installation we started the trace log on the server to monitor the errors coming from the Dynamics.
You can force the sync on the same email by modifying the one property of the email or just moving it to the different folder and then back to the
Every single time mail failed to sync with the exact same error message and it’s quite a simple one “SQL timeout expired” and this one leads us to the problem with SQL queries that are executed on email sync.
When we scrolled through the trace log we found one SQL query above the timeout messages that looked like this one:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
WITH Candidates (ActivityId) AS (SELECT ActivityId FROM EmailHashBase WITH (NOLOCK) WHERE ActivityId IN (SELECT ActivityId FROM EmailHashBase WITH (NOLOCK) WHERE HashType = 0 AND Hash IN (-1120407131, 4123, -2136194989, 1958458027, -2051031169, 424266709, 110177, 2057936289) GROUP BY ActivityId HAVING COUNT(ActivityId) >= 8) AND HashType = 1 AND Hash IN (-560784993, 1028228593, -1728357159) GROUP BY ActivityId HAVING COUNT(ActivityId) >= 4) SELECT TOP 2 a.ActivityId, a.RegardingObjectId FROM ActivityPointerBase AS a WITH (NOLOCK) WHERE a.ActivityId IN (SELECT ActivityId FROM Candidates AS c WHERE( (SELECT COUNT(*) FROM EmailHashBase AS h WHERE h.ActivityId = c.ActivityId AND h.HashType = 0 ) <= 8)) ORDER BY ROW_NUMBER() OVER (PARTITION BY a.RegardingObjectId ORDER BY a.ModifiedOn DESC) |
When we tried to execute the query in SQL Management Studio it was running for almost a minute which was a serious problem if you consider that it’s run every time a single email is synced to the Dynamics.
The problem lays in EmailHashBase table that has a lot of records in it and with all those filtering and joins it really take time to execute the query. After a call with Microsoft, we were told that it looks like smart matching fallback SQL query.
Finally when we had that information we tried to switch off the smart matching feature and everything started working like a charm. Looks like when we made our solution less smart everything is now working better.
Conclusion
Query on email hash table is causing the issue if you have the email with same subject and recipients over and over again.
You can increase the timeout limit which is not the smartest idea to do or you can just get rid of the smart matching feature because it will just kill your integration. Switching off the smart matching feature is not
There will always be a case where the
I can’t believe that I can say that a “dumb” solution made me so happy that day. Looks like that you don’t need to go the smart route every time, maybe the dumb one will just save your time and nerves.