• sql crazy kid (6/29/2013)


    Hi,

    ...

    For example a 10M records on clustered table ,to find a particular record sql will use index . This index has many non leaf nodes ,where this been defineed...

    Nodes are (index) rows in non-leaf pages (page=8KB). In fact, the number of non-leaf pages is very few compared to number of leaf pages. So few, that the number of non-leaf pages is negligible.

    Let's make rough estimation for 10M rows:

    If leaf page fits 200 data rows on average, we have 10 000 000 / 200 = 50 000 leaf pages. If each non-leaf page fits 400 index records (each index record points to a PAGE of lower level), at the first level above leaf level we need 50 000 / 400 = 125 pages. The next level up will fit all 125 rows in one page.

    So, for 10M rows you would have just 2 levels deep index, with 50 000 leaf and 125+1 non-leaf pages.

    Number of non-leaf pages is much less than 1% of leaf pages, close to 0.2%. That's why indexes are very efficient and non-leaf pages are often cached in buffer pool.

    Just to have a better picture on ratios.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths