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?