Post reply

Index dmv

  • January 18, 2012 at 4:22 am

    #391336

    Hi,

    I wondered if someone could explain this to me.

    In the AdventureWorks database (SQL 2008) I can run the following to get index details on the clustered index for the table Sales.Store table.

    select object_id,index_id,index_type_desc,index_depth,index_level,page_count from sys.dm_db_index_physical_stats (DB_ID(),14623095,1,null,'Limited')

    This tells me that the clustered index is using 101 pages, thats fine.

    When I then run the same command but with a 'Detailed' mode to see the different levels using;

    select object_id,index_id,index_type_desc,index_depth,index_level,page_count from sys.dm_db_index_physical_stats (DB_ID(),14623095,1,null,'detailed')

    I get the information back on the index, but the page counts do not add up to 101, I would of expected the lower levels (index_level) to add up to 101, but it doesn't, in fact it's actually just 1.

    object_id index_id index_level page_count

    14623095 1 0 101

    14623095 1 1 1

    I was wondering if someone could explain how this data is presented back as I'm obviously mis-understanding it.

    Thanks in advance.

    Regards,

    Nic

  • January 18, 2012 at 4:32 am

    #1435125

    Limited just shows you the leaf level of the index, so that 101 pages is the number of pages in the leaf level.

    Detailed shows you all the levels of the index, so you have the leaf level (level 0) at 101 pages and the root (level 1) at 1 page. If the index had intermediate levels as well you'd see more rows.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 18, 2012 at 4:36 am

    #1435130

    GilaMonster (1/18/2012)

    Limited just shows you the leaf level of the index, so that 101 pages is the number of pages in the leaf level.

    Detailed shows you all the levels of the index, so you have the leaf level (level 0) at 101 pages and the root (level 1) at 1 page. If the index had intermediate levels as well you'd see more rows.

    Thanks Gail, that makes sense now I think about it.

    However to confirm, if level 0 is the leaf and the root is level 1, if I did have intermediate rows what level would these be at, as there could be any number of them.

    Hope that makes sense.

    Nic

  • January 18, 2012 at 4:40 am

    #1435136

    Nic-306421 (1/18/2012)

    However to confirm, if level 0 is the leaf and the root is level 1, if I did have intermediate rows what level would these be at, as there could be any number of them.

    If you had intermediate levels the lowest intermediate would be level 1 (the leaf is always 0) and they would increment by 1 each level up the index, with the root having the highest level.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 18, 2012 at 5:08 am

    #1435156

    As usual Gail I owe you my thanks.

    Thats much clearer now.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply