Calculating table and clustered index sizes for allpages-locked tables

The formulas and examples for allpages-locked tables are listed below as a series of steps. Steps 1–6 outline the calculations for an allpages-locked table with a clustered index, giving the table size and the size of the index tree. Steps 7–12 outline the calculations for computing the space required by nonclustered indexes. All of the formulas use the maximum size of the variable-length fields. The steps are:

  1. “Calculate the data row size”

  2. “Compute the number of data pages”

  3. “Compute the size of clustered index rows”

  4. “Compute the number of clustered index pages”

  5. “Compute the total number of index pages”

  6. “Calculate allocation overhead and total pages”

  7. “Calculate the size of the leaf index row”

  8. “Calculate the number of leaf pages in the index”

  9. “Calculate the size of the nonleaf rows”

  10. “Calculate the number of non-leaf pages”

  11. “Calculate the total number of non-leaf index pages”

  12. “Calculate allocation overhead and total pages”

These formulas show how to calculate the sizes of tables and clustered indexes. If your table does not have clustered indexes, skip steps 3, 4, and 5. When you have computed the number of data pages in step 2, go to step 6 to add the number of OAM pages.

optdiag output includes the average length of data rows and index rows. You can use these values for the data and index row lengths, if you want to use average lengths instead.