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).

We believe this is due to a regression in the cardinality estimator in SQL Server 2014. We previously introduced a workaround in EF Tools which consisted on appending OPTION (QUERYTRACEON 9481) to all our schema queries (see http://entityframework.codeplex.com/workitem/2445 for more details), but unfortunately that introduced functional regressions, e.g. schema queries started failing if the user didn't have enough privileges on the database, so we had to take the workaround out. The regression appeared to be fixed but recently we have had new reports. We are following up with the SQL Server. — divega, Nov 04 '15 at 01:24
Here's the issue tracker for the same problem with EF and SQL 2016: https://github.com/aspnet/EntityFramework6/issues/4. Some people report `update statistics` commands will help... — Rory, Sep 16 '16 at 11:26

score 83 · Accepted Answer · answered Nov 01 '15 at 16:18

83

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

answered Nov 01 '15 at 16:18

Santhos

3,348
5
30
48

To clarify, was was it set to before? Was it set to 70, 80, 90, 100, 120 or 140 before? – Dai Jul 28 '16 at 09:46
1

I think my original value was 120, but it has already been some time since I did the operation. I also believe that the original value can differ depending on what version of local sql you have. – Santhos Jul 28 '16 at 17:37
1

Worked like a charm for me. Can anyone shed some light on this that why we have to set the compatibility level to 110? Are there any side effects of it? – Waheed Feb 09 '17 at 16:13
1

I'm getting this info: Valid values of the database compatibility level are 80, 90, or 100. What should I choose? – Icet Mar 16 '17 at 09:58
1

@Waheed Look into the question comments at divega's comment – Santhos Jun 03 '17 at 08:09
@Icet What sql server are you running? – Santhos Jun 03 '17 at 08:10
1

This worked for me with Visual Studio 2015 Update 3 and a SQL Server 2014 (12.0.2000). – Andrew Jul 12 '17 at 18:32
this worked for me as well, anyone know why the compatibility level fixes this? – matao Aug 08 '17 at 23:30
Setting can also be changed in the properties dialog of the database – Marco Rebsamen Mar 07 '18 at 10:05
Can anyone tell me what is significance of level 110 , what is the problem with 120 ? – Mahyar Pasarzangene Oct 01 '18 at 07:09

Bradley Mountford · Answer 2 · 2016-12-29T15:01:07.090

21

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.

edited Dec 29 '16 at 15:01

answered Dec 29 '16 at 14:39

Bradley Mountford

8,195
4
41
41

1

This worked for me. PLEASE NOTE: The accepted answer sets the database compatibility level. I believe that setting it to 110 would set the legacy cardinality estimation on. We need more people testing this answer out before trying the accepted answer. – Derreck Dean Jul 10 '17 at 20:03
2

I think that `ALTER DATABASE` is only for SQL Server 2016. Version 2014 doesn't recognize that command. It recognizes though those UPDATEs but they didn't help, the wizard had the same issue. What worked for me was changing the compatibility level. – Andrew Jul 12 '17 at 18:32
1

This should be the accepted answer. Added this to my post deployment script IF '$(DatabaseName)' = 'dev-App' ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=ON – Rob Jan 10 '18 at 04:51
1

Alter Database worked for me as well,I'm using SQL server 2017, not sure why this still exists in 2018 – afr0 Mar 05 '18 at 00:20
1

Yes, I made LEGACY_CARDINALITY_ESTIMATION=ON @mssql2016 and it worked. Do we really need to make it off again? What happens if we dont? It doesnt explain here https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql – oneNiceFriend Mar 10 '18 at 18:26
1

@oneNiceFriend Since the DB we use to generate the edmx is our dev DB, I never set it back to OFF. – Bradley Mountford Apr 24 '18 at 21:15
1

@oneNiceFriend Just found a doc that explains the different Cardinality Estimation methods in more detail with info regarding when one may be more advantageous than another https://learn.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-2017 – Bradley Mountford Apr 24 '18 at 21:23

score 3 · Answer 3 · answered Mar 29 '16 at 17:46

3

Today, my coworkers and I left the wizard alone and let it update for ~10 minutes. While it took quite a while, it did complete. This is the best solution for us (for now), since we are unable to set the compatibility level of our DB without the proper permissions.

answered Mar 29 '16 at 17:46

sparkyShorts

630
9
28

2

Yes, it does the job but in a terribly long time. – Santhos Mar 30 '16 at 08:14
@Santhos Yes, it is irritating. I spent ~30 minutes the other day updating a large model. Quite frustrating. – sparkyShorts May 19 '16 at 15:17
Mine has been going for about an hour and I'm only trying to add 1 table (this is the initial setup). Classic "halting problem", but I think I'll give up on it now... alas. – David Jan 06 '21 at 21:50

juwens · Answer 4 · 2015-11-06T11:29:36.277

Changing SQL Server compatibility level or trace-flag 9481 is no option for me.

I gave EntityFramework Reverse POCO Generator a try.

https://visualstudiogallery.msdn.microsoft.com/ee4fcff9-0c4c-4179-afd9-7a2fb90f5838

It's a configurable generic T4 Template and works very well till now.

It even has an Option for the mentioned trace-flag

IncludeQueryTraceOn9481Flag = false; // If SqlServer 2014 appears frozen / take a long time when this file is saved, try setting this to true (you will also need elevated privileges).

Ironically it works fast even if the flag is off :) Seems like they are using different queries for metadata compared to the VS EF Designer.

score 1 · Answer 5 · answered Nov 16 '17 at 16:42

I still had to do this with Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64) Jul 3 2017 02:25:44 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) using Entity Framework 6.2.0. How in the world has this not yet been solved?!

fusion27 · Answer 6 · 2018-05-17T17:55:01.250

MariaDB Lethargic Manual Remedy

Using EF6, VisualStudio 2015 against MariaDB 10.2.
Just like @Santhos indicated, blowing away a ton of time for me. Seems like VisualStudio is slow at parsing that huge XML file (my development machine only has 8gb RAM). No joke, about 15 minutes to refresh.
I learned that manually 'resetting' a couple of files, then proceeding through the GUI update squared me away quickly. I'm in and out with shiny new models ~1-2 mins.

Manual Refresh Steps

Replace the contents of your .edmx.diagram file with the EDMX Diagram Boilerplate below
Replace the contents of your .edmx file with the EDMX Runtime Boilerplate below
Return to VisualStudio, double-click your EDMX > in the right click in the empty screen > Update Models from Database

EDMX Diagram Boilerplate

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
 <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2009/11/edmx">
    <!-- Diagram content (shape and connector positions) -->
    <edmx:Diagrams>
      <Diagram DiagramId="820459acb0f543cfaf7db8643f38c2d6" Name="Diagram1" ZoomLevel="85">
        </Diagram>
    </edmx:Diagrams>
  </edmx:Designer>
</edmx:Edmx>

EDMX Runtime Boilerplate

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
    <Schema Namespace="ShareDirectModel.Store" Provider="MySql.Data.MySqlClient" ProviderManifestToken="5.5" Alias="Self" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
        <EntityContainer Name="ShareDirectModelStoreContainer">
          </EntityContainer>
      </Schema></edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="ShareDirectModel" Alias="Self" annotation:UseStrongSpatialTypes="false" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
        <EntityContainer Name="ShareDirectContext" annotation:LazyLoadingEnabled="true">
          </EntityContainer>
        </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
        <EntityContainerMapping StorageEntityContainer="ShareDirectModelStoreContainer" CdmEntityContainer="ShareDirectContext">
          </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <Designer xmlns="http://schemas.microsoft.com/ado/2009/11/edmx">
    <Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </Connection>
    <Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
        <DesignerProperty Name="EnablePluralization" Value="true" />
        <DesignerProperty Name="IncludeForeignKeysInModel" Value="true" />
        <DesignerProperty Name="UseLegacyProvider" Value="false" />
        <DesignerProperty Name="CodeGenerationStrategy" Value="None" />
        <DesignerProperty Name="DDLGenerationTemplate" Value="$(VSEFTools)\DBGen\SSDLToMySQL.tt" />
      </DesignerInfoPropertySet>
    </Options>
    <!-- Diagram content (shape and connector positions) -->
    <Diagrams></Diagrams>
  </Designer>
</edmx:Edmx>

Ahmad Pujianto · Answer 7 · 2021-05-04T03:17:01.277

-1

This kind of problem usually happen because the server running SQL server it self is not in good condition. Maybe the disk space or memory on the server is getting too low to finish the task.

Check the server that running your DB.

edited May 04 '21 at 03:17

answered Aug 18 '20 at 08:18

Ahmad Pujianto

309
1
3
11

Update wizard not responding

7 Answers7

MariaDB Lethargic Manual Remedy

Manual Refresh Steps

EDMX Diagram Boilerplate

EDMX Runtime Boilerplate

Linked