SQL job get blocked after some time by a process on master DB

Question

I have a job which performs following steps on high level
1.) Load Stage table
2.) Load Fact table
3.) Apply lots of rules on fact table, so lots of updates

This was working fine for more than a year but now it gets stuck after applying some of the rules. Every time gets stuck at a different rule.

SP_WHOISACTIVE suggests that some other session is blocking the update of data

When I looked up the blocking session, its a sleeping session initiated by SA on master. It seems like for some reason SQL decides to block this execution after some time.
Please suggest

My sql server is hosted in Azure using elastic pool and i have already tried to increase the db storage and cores by 25% but no luck

Answer 1

@Leekha,Mohit,IN-Kolkata I search and found few posts talking about this. if this doesn't help then please raise a support case for deeper investigation.

Background Index Creation on SQL Server - Database Administrators Stack Exchange

Regards

Geetha

Answer 2

Azure SQL Database has a feature known as automatic indexing. You can read more about it in the topic Automatic tuning in Azure SQL Database and Azure SQL Managed Instance. The topic also has reference to documentation how you can enable and disable this feature. Maybe you should disable it before you start your load, and re-enable it when you are done.