These are the Few Question, which every DBA want to get.
- How much temporary space is required to create or rebuild a index ?
- Whether it matters if you are creating/rebuilding a unique or a non-unique index ?
- Whether it matters if you are creating/rebuilding an index on a partitioned or a non-partitioned table ?
- Whether it matters if the index is a narrow single column index or a wide index that encompasses all the columns in the table ?
- Whether it matters if the index has included columns ?
- What is the effect on the amount of temporary space and performance if you want the maximum concurrency while creating/rebuilding an index ?
- What is the effect on performance and concurrency if you want to use the least amount of temporary space ?
Let’s us understand this via practical example
These are the Table specifications, where we wanted to create a index
- Size of the table = 1159633 pages = 8.85 GB
- Number of rows in the table = 179743073
- Size of each row = 42 bytes
- Number of columns in the index = 3 => 10 bytes
Here are the Results
- Size of the clustered index created = 1159650 pages = 8.85 GB
- Size of the mapping index in the TEMPDB = 603848 pages = 4.6 GB (approximately 52% of the index size)
- Space used in TEMPDB for the sort runs = 1293728 pages = 9.88 GB (approximately 112% of the index size)
- Total space used in TEMPDB = 1897576 pages = 14.48 GB (approximately 164% of the index size)
Therefore, to create the clustered index online, you need at least 8.85 GB of free space in the user database and 14.48 GB of free space in TEMPDB. After the clustered index is created, the space used by the table is freed, but during the creation of the clustered index, the table and the index coexist. Therefore, you must free space in the user database equal to the size of the index that is being created. If you choose the SORT_IN_TEMPDB = OFF option, you need a total of 23.33 GB of free space in the user database(for a 8.85 GB table).
If there are concurrent transactions on the table while you are creating the index, you also need additional space in TEMPDB for the version store. In one test, we updated 5% of the rows in the table while creating the clustered index online, and the version store was measured to be 143408 pages = 1.1 GB for this operation.
To Understand it better and in detailed manner, please download Case Study, which describes the various options for index create and rebuild operations, and their effects on performance, concurrency, and resource requirements. This paper also provides some best practice recommendations.