Suddenly Indexes taking too much time to complete (more than 1 hr) where it was initially taking 15 mins on Azure managed SQL Instance
Exact what is in that “index run script”? I would guess that you are rebuilding the indexes, but maybe it is something else?
If you are rebuilding the indexes, by what criteria does the script use to determine to rebuild an index? Are you using Ola Hallengren’s solution, or something else?
Since you say that the database locks up, it sounds as if you are using an offline rebuild, which is the default. You can use a online rebuild instead. This reduces the amount of locking considerably. Instead of the table being locked for the entire rebuild, there are only two short locks, one in the beginning and one in the end.
To run an online rebuild, and the clause WITH (ONLINE = ON) to the ALTER INDEX command.
Read more here: Source link
