Prevent DTU exhaustion when creating a new index

Question

We're using Azure SQL as a single database and under the DTU pricing model. We have a table with ~50M records, and we'd like to add a new non-clustered index on a single string attribute.

The problem is that this is a production database. If I were to use the simple TSQL syntax of

CREATE NONCLUSTERED INDEX [IndexName]
ON [dbo].[TableName]([FieldName] ASC);
GO

The index creation will peg DTUs to 100% for several minutes, which essentially starves any queries from our application code.

Is there a way in Azure SQL to instruct SQL Server "Hey, only use X DTUs for this indexing operation"?

Answer 1

You can use option MAXDOP to limit the number of processors used.

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON) ;

You may consider using the Resumable Online Index Rebuild feature so you can schedule X executions of one minute of those indexes.

ALTER INDEX [ix_CustomerIDs]
ON [ContosoSales].[ConstosoTransactionData]
REBUILD
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 1 MINUTES);
GO

ALTER INDEX ix_CustomerIDs ON [ContosoSales].[ConstosoTransactionData] PAUSE

ALTER INDEX ix_CustomerIDs ON [ContosoSales].[ConstosoTransactionData] RESUME