When it comes to managing indexes in SQL Server, there are several costs associated with rebuilding or converting an index. In this article, we will explore these costs and provide a formula to estimate the extra space required for index maintenance.

Temporary Costs

Before diving into the formula, let’s first understand the temporary costs involved in index rebuilding. When an index is rebuilt, a temporary object called a Temporary Mapping Index is created to handle any transactions while the index is being rebuilt. This temporary object takes up extra space, although the exact amount is not specified. Additionally, if the current or assigned Fill Factor of the rebuild is less than the current average page density, new pages will need to be created to increase the empty space in existing pages. These new pages will also contain empty space according to the Fill Factor. While these temporary costs are important, we won’t be discussing them further in this article.

Permanent Costs

The permanent costs of index rebuilding are the extra space requirements resulting from reducing the Fill Factor. The extra pages produced can only be used by the index that occupies them. There are several reasons why calculating the size of the extra space required is important:

• Impact on backups, restores, and index maintenance: If you have a particularly large index or plan on reducing the Fill Factor for a large number of indexes, the rebuild will increase the size and time it takes to perform these operations. You may also need to increase the disk space for storing backups.
• Performance impact: On machines with limited memory, queries will require more pages to be loaded into memory, potentially driving other indexes out of memory. This can result in performance issues as these indexes need to be retrieved from disk when they are used again.

The Formula

To estimate the extra space required for index maintenance, we can use the following formula:

SavingsMB = (PageCount – (AvgPageDensity / FillFactor * PageCount)) / 128

If the number returned is positive, it indicates savings in megabytes. If the number returned is negative, it indicates a cost in megabytes.

Example Usage

Let’s consider an example where we have a massive non-compressed, non-partitioned, non-LOB, row-store Clustered Index. The index maintenance is currently set up to rebuild it using a Fill Factor of 100, but we want to change it to a Fill Factor of 70. To estimate the extra space required, we can use the formula:

SavingsMB = (PageCount – (AvgPageDensity / FillFactor * PageCount)) / 128

By plugging in the values for the current Fill Factor (100) and the desired Fill Factor (70), we can calculate the extra space required.

Conclusion

Understanding the costs associated with rebuilding indexes in SQL Server is crucial for effective index maintenance. By using the formula provided in this article, you can estimate the extra space required and make informed decisions about index maintenance. Remember to always test and validate any formulas or techniques before implementing them in a production environment.

References:

• https://docs.microsoft.com/en-us/sql/relational-databases/indexes/how-online-index-operations-work
• https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disk-space-requirements-for-index-ddl-operations
• https://www.sqlservercentral.com/articles/some-t-sql-inserts-do-follow-the-fill-factor-sql-oolie

© Copyright by Jeff Moden – 23 July 2019 – All Rights Reserved