Post reply

XML index statistics

  • November 7, 2017 at 12:08 am

    #341199

    Hi ,

    How can I see XML index statistics like other indexes.
    Using the DBCC SHOW_STATISTICS () I am able to see the index statistics other than XML indexes.
    Using the sys.dm_db_index_physical_stats  DMV I can see the storage space of XML indexes.
    Is there any way to see the stats output like normal indexes using DBCC for XML indexes.

    Thanks
    Kareemulla.

  • November 7, 2017 at 2:27 am

    #1966938

    XML indexes are not the same as a standard B+Tree clustered or nonclustered index. It doesn't use a histogram and density graph to define the index. Instead, it's a BLOB stored with the table. There is no real standard maintenance you have to run on these either. Read more about them here.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • November 7, 2017 at 7:20 am

    #1966989

    Although you can't actually see the XML indexes, you can see what the Primary Index would look like by prepping an XML "Document" and looking at what they call the "Edge Table", which is not much more than an "Adjacency List", which is also known as a "Parent-Child" hierarchical table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

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