Timeout when create index on a large table in Azure

0
votes

I have table contains more than 50 million records in Azure. I'm trying to create a nonclustered index on it using follow statment

create nonclustered index market_index_1 on MarketData(symbol, currency) with(online=on)

But I get a error message.

Msg -2, Level 11, State 0, Line 0 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Any suggestions would be greatly appreciated.

1
azureazure-sql-database

1 Answers

1
votes

Check out the Azure SQL Database Resource Limits document. Then compare the error code with the error codes listed on this document.

With data of that size I believe the only way to create new index in that table would be:

  • Create new table with same structure and only one clustered index
  • Copy the data from original table into the new one
  • Truncate the original table
  • Create desired indexes
  • Copy data back into original table

Note that moving the data between the tables will potentially once again exceed the resource limits, so you might have to do these operations in chunks.

Other possible approach is to upgrade the database server to the new Preview Version of Azure SQL Database (note: you cannot downgrade the server later!)