Beware when setting the database compatibility level on a CRM 2016 system

Recently, we came across a bizarre SQL timeout issue when opening a record. After digging deeper we tracked down the query that was causing the problem. The issue is, if you have a Dynamics CRM 2016 system with an entity which has over 5 secured fields (via Field Level Security) and the SQL server database compatibility level is set to a value higher than 110, the SQL query optimizer incorrectly estimates the number of records.

For example,

Here is the query that causes the problem

The SQL Query Optimizer badly assumes/guesses the number of rows, as you can see below, the estimated number of rows is 10 even though there are no rows!

What happens is, SQL compounds, if you have 10 secured fields, the estimated number of rows is 10 ^ 10 = 10,000,000,000!!!. If you run the query and tell SQL to show you the estimated or actual query plan, you’ll see a warning ‘No join predicate’. This is actually a serious error!

The fix for now is to set the SQL database compatibility level to 110.