FIX – For performance issues CRM 2016 vs SQL 2016 on-premisses

After we migrated SQL from 2012 to 2016 we noticed that the performance of CRM dynamics was poor. The cause turned out to be in the CE (Cardinality Estimation).
SQL 2016 gives problems with updating the statistics of the CRM database, followed by wrong exuction plans

So how to fix this problem?

  • Easy in SQL 2016 you are able to set the CE on database scope in legacy modus.
-- Step 1 - Turn on the LEGACY_CARDINALITY_ESTIMATION option
use <database_name, sysname, your_database_name>
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO