Azure SQL Database -> Add Index -> Object '/.rem' has been disconnected or does not exist at the server

Question

I have a quite large table in my database, current size is 2 852,305 MB. I can query this database quite fast using a localdb instance on my developer machine, around 100 ms.

However with the exact same query and data it takes a whooping 70 seconds on Azure SQL Database with 20 DTUs (S1)!

I was reading up on this, TL;DR: Using indices for the query can fix the problem.

http://capesean.co.za/fixing-slow-performance-with-azure-sql-database/

I'm using Entity Framework so I added an Index and tried to run Update-Database from PMC as usual.

https://docs.microsoft.com/en-us/ef/ef6/modeling/code-first/data-annotations#multiple-column-indexes

However the Index is not added, I keep getting the error Object '/.rem' has been disconnected or does not exist at the server.. I have read about the same error code below but given the circumstances I do not control the Lifetime of the object.

"Object has been disconnected or does not exist at the server" exception

What can I do to fix this?

It is probably not a timeout issue either, both migration and ApplicationDbContext has bumped timeouts.

internal sealed class Configuration : DbMigrationsConfiguration<ApplicationDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        CommandTimeout = 10000; // migration timeout
    }
}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext()
        : base("DefaultConnection", throwIfV1Schema: false)
    {
        //Log to output
        Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

        //Prevent timeouts
        Database.CommandTimeout = 1800;
    }
    ...

Complete stack trace:

System.Runtime.Remoting.RemotingException: Object '/2f49096e_0c2e_49d6_a7a0_7ffbe823cf87/yxyu+vuywlowzb7myk8fhrw4_7932.rem' has been disconnected or does not exist at the server. at System.Data.Entity.Migrations.Design.ToolingFacade.ToolLogger.Verbose(String sql) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinNewTransaction(IEnumerable1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection) at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass32.<ExecuteStatements>b__30() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements, DbTransaction existingTransaction) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable1 operations, IEnumerable1 systemOperations, Boolean downgrading, Boolean auto) at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration) at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClasse.b__d() at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration) at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore() at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run() Object '/2f49096e_0c2e_49d6_a7a0_7ffbe823cf87/yxyu+vuywlowzb7myk8fhrw4_7932.rem' has been disconnected or does not exist at the server.

Ogglas Ogglas · Accepted Answer · 2018-11-17T23:55:06

The code for the migration that failed looked like this:

    public override void Up()
    {
        CreateIndex("dbo.MyTable", new[] { "Column1", "Column2", "Column3" }, name: "IX_MyIndex");
    }

To prevent errors in Visual Studio I ran the command Update-Database -script, copied the script and ran it using SQL Management Studio directly against the Azure database. Index created looked like this:

CREATE INDEX [IX_MyIndex] ON [dbo].[MyTable]([Column1], [Column2], [Column3])

This did the trick! The query took around 8 minutes to run. Performance is now similar to localdb:

The good thing about Update-Database -script is that you get correct values for __MigrationHistory. I could have copied these from my localdb but I think this is cleaner.

Running any database code will give you this error if there is a mismatch:

{"Message":"An error has occurred.","ExceptionMessage":"The model backing the 'ApplicationDbContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269)

Sent this question over to Microsoft so I hope they will issue a fix for it.

https://twitter.com/Ogglas/status/1063927246245822464

Azure SQL Database -> Add Index -> Object '/.rem' has been disconnected or does not exist at the server

1 Answers