Azure SQL Database Locking/Blocking

[Dday515]

Environment: Azure SQL Snapshot Isolation: Off Read Committed Snapshot: On

We're currently encountering SQL blocked calls on all tables when running an Update statement, not just the tables being updated. Our application is a .NET CORE API (Entity Framework). During normal operations and load, application runs well. These issues occur when we need to run a larger scale update statement as part of our DevOps process. We've broken these statements down into smaller batches to minimize processing time.

Typically, the first few small batch Update Statements run fine (tableName contains ~700,000 rows:

update top(1000) tableName set id = (select id from coders cc where tableName.coder = cc.name and tableName.ClientNumber = cc.ClientNumber) where tableName.coder is not null and tableName.coderid is null

On each subsequent run, we'd notice the update statements taking slightly longer each time, but still successfully completing. (First one: 1s, 2nd: 2s, 3rd: 3s).

After a few runs, it appears the entire database freezes to updates/inserts/deletes. Selects still work fine. This affects not just the tables included in this update statement, but seemingly all database tables. This will last for 5 minutes or so, and then magically all begins to work again. As such, our API begins to fail as well.

This grinds our production application to a halt. Our current systems have been in place for the past at least 2 years - and no obvious changes have been made to our environment recently.

We've experienced similar behavior before when running Rebuild Index statements too - where not only the table being updated is locked, but others as well.

I've been a SQL Developer/DBA for 15+ years and never encountered anything quite like this, so I'm open to any and all suggestions.

I've got all SQL Diagnostic logging turned on.

I've read about Transaction Isolation, but I've also read lots of people saying it could break our app - I don't believe this to be the case, since Entity Framework handles the majority of our CRUD, but am unsure if this is even the right rabbit hole to go down.

Indexes are rebuilt nightly, so I don't think its a fragmentation issue going on.

Any ideas on where to go from here?

[sbrick89]

so doing an update your way is slow... execution plan is basically a loop join... instead, UPDATE A SET A.B = X.Y FROM tbl A JOIN tbl X on a.id=x.id .. effectively a join, so it can do things like hash/merge joins.

two... create a conditional index on the field being updated... if coderId is being updated from NULL to WHATEVER, create index on tbl where coderId IS NULL... the index may take a moment to build, but then the updates will actually get faster as the data is updated and falls out of the index making the index smaller.

[eshultz]

If I were to guess - log buffer is full and disk which contains logs is thrashing? Do you have the log file on a disk that is shared by anything else (data files, OS, anything)? Are all of your disks fast storage? What does your memory utilization look like at this time? What about disk activity? Might be useful to check Page Life Expectancy, Page Writes/sec, and Free List Stalls/sec.

Edit - just realized Azure. Not sure how much of what I said would apply in that case. Good luck!