I don’t understand Step2 #8 Calculate the number of number of non-leaf pages in the index. I am not sure I understand the “Level”.
I don’t understand how they arrived at
Num_Index_Pages = 1000/(253)+ 1000/(252) + 1000/(251) = 1 + 2 + 40 = 43
“which is the number of pages described in the example.”
1000/253 does not give 1, and 1000/252 does not give 2…What am I missing with regard to the “level” and how can I plug my values of 508 and 476 in and be confident with the result.
Attempting to implement the same, I ended up using this to approximate at a decent level of accuracy:
WHILE (@NonLeafLevels > 1) BEGIN DECLARE @TempIndexPages FLOAT; -- TempIndexPages may be exceedingly small, so catch any arith overflows and call it 0 BEGIN TRY SET @TempIndexPages = @NumLeafPages / POWER(@IndexRowsPerPage, @NonLeafLevels); SET @NumIndexPages = @NumIndexPages + @TempIndexPages; SET @NonLeafLevels = @NonLeafLevels - 1; END TRY BEGIN CATCH SET @NonLeafLevels = @NonLeafLevels - 1; END CATCH END;