Update wizard not responding

Question

Every time I need to update my emdx from database, the update wizard takes an incredible amount of time to do so rendering itself as not responding once you hit the finish (as finish the update) button.

I use Visual Studio 2015 and LocalDb SQL Server 2014. Some people suggested to install the Service Pack 1 to address the issue. I have installed the SP1 for LocalDb, but it has not helped. My installation of VS2015 is also rather new.

I have the latest Entity Framework 6 version (from nuget).

Answer 1

Setting the compatibility level of the database to 110 has worked for me.

To check the compatibility level, run this script:

select compatibility_level from sys.databases where name = '<YOUR_DB_NAME>'

To set the compatibility level, use this script:

alter database <YOUR_DB_NAME> set compatibility_level = 110

Answer 2

Running the following on the DB worked for me:

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=ON

Then, after the update, setting it back using:

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=OFF

This is per this thread over at the EF6 repo on Github.

It should be noted that the following is also reported in that thread to work though I have not tested it because the former worked so well for me:

UPDATE STATISTICS sys.syscolpars
UPDATE STATISTICS sys.sysschobjs
UPDATE STATISTICS sys.syssingleobjrefs
UPDATE STATISTICS sys.sysiscols

They also punted this back to the SQL Server team and opened up this issue over at Microsoft Connect.