1

New to Azure SQL. Simple example to ask question.
Table Cars. Want to add an non clustered index to Cars table. Takes a long time, say 30 min during staging test deploy.

When upgrading production Azure SQL, can we leave the database online while building a table index? During this time while the table is building, can applications send inserts/updates to this table?

Or do we need to take the production DB offline for 30 to build this index?

Improve this question

3 Answers 3

Reset to default
2

You should absolutely be able to access the table while the index builds. Now, depending on your service tier in Azure, you might be using a lot of your resources to build this index, and that might impact user access, but otherwise, no. The index will build, but access to the table will continue.

Improve this answer
2
  • So will this do the trick? CREATE INDEX ... ON ... WITH (ONLINE = ON);
    – nanonerd
    Commented Oct 23, 2018 at 14:28
  • Read the documentation: learn.microsoft.com/en-us/sql/t-sql/statements/… For a nonclustered index, it can help with updates to the table while the index is being created, but otherwise, it doesn't affect the process at all for the nonclustered index.
    – Grant Fritchey
    Commented Oct 23, 2018 at 14:52
2

Yes, leave online and inserts and updates happen while indexing occurs in the background. See Guidelines for online index operations

Improve this answer
-1

You can consider take advantage of the resumable online index creation feature to distribute the time needed to create the index over many hours, assigning one or two minutes (or X seconds) every X minutes of time to create the index. For more information, please read this resource.

ALTER INDEX test_idx on test_table PAUSE  

ALTER INDEX test_idx on test_table RESUME
Improve this answer
3
  • How does this help in this case? The index will still take the same amount of time to build and this would be manual work to stop and start it. Also from what I am reading this is for when you have database issues such as a failure or a lack of resources rather then just taking a long time to complete.
    – Joe W
    Commented Oct 22, 2018 at 19:59
  • But it creates manual work that needs to be done in order to get the index fully built (and it will take longer to get completed and the index could be critical to operation) and this solution does not address the question about if the database can be online or not during the index build. From the way I read the question performance of the index build is not an issue at the current time and it was just a question of can it be done with the database online.
    – Joe W
    Commented Oct 22, 2018 at 20:33
  • The 30 minutes for index creation still exists and in fact it probably takes longer to create since there will be new data to be indexed as it goes along. All this does is spread the time to create it over a longer period of time which will either take manual work to do (to manually start and stop it) or the creation of a script to do it. Either way the index is still going to take the same amount of processing time to complete and overall it is going to take a significant longer amount of time to complete which can defeat the purpose of adding an index if you don't get it built speedily
    – Joe W
    Commented Oct 22, 2018 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.