CODEWITHSUNDEEP
indexingazure-sql-database
nanonerd
nanonerd

Reputation: 1984

azure sql - create index, does DB have to be offline?

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?

Upvotes: 1

Views: 783

Answers (2)

Alberto Morillo
Alberto Morillo

Reputation: 15648

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 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

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294267

CREATE INDEX does not 'take the database offline', but normally it will block all operations on the Cars table until it completes.

In Azure SQL DB you have the option to issue ONLINE create index operations, which will allow concurrent access to the table while the index is being built. See Perform Index Operations Online:

CREATE INDEX ... ON ... WITH (ONLINE = ON);

Also, 30 min is very big for CREATE INDEX. I would investigate if the time is not somehow spent almost entirely being blocked.

Upvotes: 2

Related Questions