Skip to content

Index creation on big table

On this page are some statistics and guidelines to help when you need to create an index on a very big table.

Index creation stats

2024-11-28 Index on Transaction table in TransactionsDB

  • Table had around 266672624 rows
  • Index created with this command CREATE INDEX IX_Transaction_TransactionFlowId ON [Transaction] (TransactionFlowId) WITH (ONLINE=ON, MAXDOP=2)
  • Index creation time: 00:27:17.061
  • DB DTU: Scaled up from 500 to 1000.

Before running with these settings we also tried running with MAXDOP=1 and 500 DTUs, but then it did not complete within 40+ minutes and was cancelled. We also tried running with RESUMABLE=ON, but then query latency was immediately increased to too high levels and we cancelled it after a couple of minutes.

Resource usage

image.png

Query latency impact

(index creation running between the red lines)
image.png