One of the problems with SQL Azure databases (pre V12) is the inability to build / rebuild the large indexes because of the 2 GB transaction size limit. According to the blog this is no more a problem with SQL Azure V12 databases. However this doesn’t necessarily means a large index rebuild can succeed in SQL Azure V12. SQL Azure V12 is more transparent and we can know more details about the physical limits of the database and its usage, that help us understand several internal details which unknown in the prior version.
A few critical factors that needs to be considered before starting an index rebuild are:
Table size and the columns involved in the index.
The size of the index is proportional to the (size of the key column * number of rows). For example if we are building an index on an int coumn and a char(100) then each index record size if 104 bytes. If we have a million rows this will be 1000000 * 104 = ~ 100MB.
Available log size
Query sys.database_files to know the available log space.
Log rate (the rate at which log is growing)
One should also know the max log rate of the database and the rate at which log is growing without the index rebuild transaction. This is necessary to make sure you don’t hit log full before the index rebuild completes, otherwise rebuild may run of log space and fails.
One simple idea to measure the log rate of the database is to keep an active transaction on one connection, and throttle the database with concurrent connections and measure the log file growth and divide it by the time duration.
Online vs offline index build
Not but not the least, make sure use the online index rebuild option and not offline (index rebuild with online = on option) index rebuild so that the table is not locked through out the index rebuild operation.