Optimizing a Terabyte-Scale Azure SQL Database

Question Description:
A customer designed a database schema for a terabyte-scale Azure SQL database, but the original design did not include a unique key. As data volumes grow, performance issues become more severe.

1. To address these performance issues, Azure SQL uses hidden indexes that use unique identifiers, which have a maximum limit of 2,147,483,648.
2. If the number of data rows in the table exceeds 2.1 billion, performing an insert or update operation will result in the following error:

Message 666, Level 16, State 2, Line 25

These errors can lead to partial or complete database outage, which can have a serious impact on business operations.

Mitigation strategies:

1. Quick solution:

• Specify ONLINE=ON and RESUMABLE=ON to make the index creation online and resumable. This allows the operation to use smaller transactions, so that if it fails for any reason, it can be resumed from the point of failure.

  2. Permanent fixes:

• Rebuild the clustered index with a unique key by adding a new column. Here are the steps:

References

1. Unique identifier
2. How to design a clustered index
3. Create a clustered index