Calculate Num_Index_Pages for sizing design

Posted on

Question :

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 have Num_Leaf_Pages=508 & Index_Rows_Per_Page=476

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.

Answer :

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;

Leave a Reply

Your email address will not be published. Required fields are marked *