Gayan “Performance Wizard” Perera continues to live up to his nickname with another awesome performance tip.

tl;dr

Beware setting the SQL database compatibility level to a level higher than 110 on a CRM 2016 system.

The scenario

You have an entity with more than 5 secured fields. When the record is opened it either times out or takes more than 3 minutes to open the record. The reason is, CRM creates a query like this:

SELECT xyz.fields FROM custom_Entity as xyz
LEFT OUTER JOIN fn_UserSharedAttributeAccess(user, 
   field, entity) as poaa0 on ...
LEFT OUTER JOIN fn_UserSharedAttributeAccess(user, 
   field, entity) as poaa1 on ...
LEFT OUTER JOIN fn_UserSharedAttributeAccess(user, 
   field, entity) as poaa2 on ...
...etc

It seems, if the compatibility is set to a level higher than 110 on a CRM 2016 system the query optimizer estimates an incorrect number of records, does some weird [10^Number of Secured Fields] calculation and causes CRM to timeout or take a very long time to execute. E.g. if you have 10 secured fields, SQL estimates that you have 10^10 = 10,000,000,000 records generating a suboptimal execution plan that results in poor performance.