Create NonClustered online vs Create NonClustered offline

All we need is an easy explanation of the problem, so here it is.

I understand that Nonclustered index uses bigger space than clustered index. Azure SQL provides option to create Nonclustered index using both ONLINE = { ON | OFF }.

  1. How is the NCI creation different for online and offline?
  2. Will there be any difference in disc size consumed while creating online and offline index? If yes, is there a way to calculate the disc size of online/offline index creation?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

  1. How is the NCI creation different for online and offline?

Per Perform Index Operations Online:

The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations.

In other words, the index (in your case the nonclustered index) being created won’t be exclusively locked from access during the entirety of the operation. Therefore other queries will be able to utilize it while it’s undergoing such operation ONLINE.

If it was created using the OFFLINE option then the index would be exclusively locked from access until it’s completely finished being created.

  1. Will there be any difference in disc size consumed while creating online and offline index? If yes, is there a way to calculate the disc size of online/offline index creation?

I believe the amount of disk space difference should be rather negligible usually but there can be a difference per Temporary Disk Space for Online Index Operations:

Online index operations use row versioning to isolate the index operation from the effects of modifications made by other transactions. This avoids the need for requesting share locks on rows that have been read. Concurrent user update and delete operations during online index operations require space for version records in tempdb.

So one of the biggest factors that would contribute to disk space overhead during an ONLINE operation would be if there are concurrent data changes to the fields that are part of the index. On a very heavily transactional database you may notice the difference in disk space overhead, but generally I believe it’s not worth worrying about.

This makes the potential size difference variable based on the write-heaviness of your table during the index operation, and therefore not possible to proactively calculate before running the index operation.

Other related product documentation:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply