Post reply

Index Fragmentation

  • November 8, 2011 at 7:23 am

    #390795

    Hi Guys

    Please help with the following or point me in the right direction

    Am I right in the following: A Clustered index becomes fragmented when the Index leaf pages are not in order - This is caused by page splits...

    How does a non clustered index become fragmented?

    What types of fragmentation are there and do they affect Clustered and Non-clustered indexes in the same way.

    Thanks

    Derek

  • November 8, 2011 at 7:27 am

    #1404524

    derekr 43208 (11/8/2011)

    Am I right in the following: A Clustered index becomes fragmented when the Index leaf pages are not in order - This is caused by page splits...

    How does a non clustered index become fragmented?

    Exactly the same way.

    What types of fragmentation are there and do they affect Clustered and Non-clustered indexes in the same way.

    Logical fragmentation - pages out of order - and extent fragmentation - next extent is not physically adjacent. Absolutely no difference between how they affect a clustered index vs a nonclustered.

    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

  • November 8, 2011 at 7:35 am

    #1404543

    Thanks

    If I have a table with a clustered index and a non-clustered index on it

    If the Clustered index is fragmented, does that mean that the non-clustered index is also fragmented?

    Thanks

  • November 8, 2011 at 7:38 am

    #1404544

    derekr 43208 (11/8/2011)

    Thanks

    If I have a table with a clustered index and a non-clustered index on it

    If the Clustered index is fragmented, does that mean that the non-clustered index is also fragmented?

    Thanks

    Absolutely no link between the 2 in that matter.

  • November 8, 2011 at 7:41 am

    #1404547

    Thanks

    Would that still apply even if the 2 indexes were covering the same columns?

    Thanks

  • November 8, 2011 at 7:42 am

    #1404550

    derekr 43208 (11/8/2011)

    Thanks

    Would that still apply even if the 2 indexes were covering the same columns?

    Yes. An index's fragmentation is a function of that index's leaf pages, the percentage of those pages that are out of order, absolutely nothing else.

    If two indexes are created on exactly the same columns (key and include), have the same fill factor and were created or rebuilt at exactly the same time, then they will both be subject to more or less the same number of page splits and will likely have a similar fragmentation.

    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

  • November 8, 2011 at 7:43 am

    #1404553

    If the 2 indexes are 100% identical, then yes they should be fragmented in the same way. Assuming they were created at the same time, or that at some point they were rebuild at the same time.

    That has nothing to do with clustered VS NC and all with their definition.

  • November 8, 2011 at 7:50 am

    #1404558

    Thanks

    I though that because the non-clustered indexes leaf pages have a pointer to the clustered index, if the Clustered index was fragmented, then NC index would also be fragmented.

    Am I missing something here?

    Thanks

  • November 8, 2011 at 7:53 am

    #1404566

    Fragmentation is solely and only a measure of how 'out of order' an index's leaf pages are based on the index key. That's all it is.

    So if index X (and it doesn't matter in the slightest if it's clustered or nonclustered) has every single leaf page physically in the order of the index key, that index has 0% fragmentation. If half of the pages are out of order (physical page order doesn't match key order) then that index is 50% fragmented.

    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

  • November 8, 2011 at 11:10 pm

    #1404964

    GilaMonster (11/8/2011)

    Fragmentation is solely and only a measure of how 'out of order' an index's leaf pages are based on the index key. That's all it is.

    So if index X (and it doesn't matter in the slightest if it's clustered or nonclustered) has every single leaf page physically in the order of the index key, that index has 0% fragmentation. If half of the pages are out of order (physical page order doesn't match key order) then that index is 50% fragmented.

    Thanks

    When talking about an Index Key, is that the data in the columns it covers?

    So for a Clustered index, because it is the actual table data, if the data pages itself are not in order - Fragmentation.

    NC Index, if the leaf pages order does not match the table/clustered index pages order - Fragmentation.

    Is that right?

    Am I correct in that a NC Index does point to the Clustered index?

    How do we get forwarding pointers?

    Thanks a lot.

    Derek

  • November 9, 2011 at 12:10 am

    #1404984

    derekr 43208 (11/8/2011)

    When talking about an Index Key, is that the data in the columns it covers?

    No, just the column defined as the index key, the column(s) that the index b-tree is built on and the index ordered on. Not the include columns

    NC Index, if the leaf pages order does not match the table/clustered index pages order - Fragmentation.

    No, not at all. An index is fragmented if its pages physical order do not match its key's order. Purely and only. Nothing to do with any other index anywhere.

    Am I correct in that a NC Index does point to the Clustered index?

    Yup

    How do we get forwarding pointers?

    Never with an index. Only possible with a heap.

    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

  • November 9, 2011 at 11:23 pm

    #1405518

    NC Index, if the leaf pages order does not match the table/clustered index pages order - Fragmentation

    No, not at all. An index is fragmented if its pages physical order do not match its key's order. Purely and only. Nothing to do with any other index anywhere.

    Thanks I'm just struggling a bit to understand the NC index fragmentation concept

    So when a NC index is created, its leaf pages match the order of the data pages - Fragmentation happens when the order of the index pages don't match the order of the data pages?

    Can you give me some insight into physical fragmentation, is that the same thing as what we have been discussing?

  • November 9, 2011 at 11:26 pm

    #1405522

    How do we get forwarding pointers?

    Never with an index. Only possible with a heap.

    Can this happen with a table with just non-clustered indexes on it.

    Thanks

  • November 10, 2011 at 2:55 am

    #1405579

    derekr 43208 (11/9/2011)

    How do we get forwarding pointers?

    Never with an index. Only possible with a heap.

    Can this happen with a table with just non-clustered indexes on it.

    The definition of 'heap' is a table without a clustered index.

    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

  • November 10, 2011 at 3:03 am

    #1405587

    derekr 43208 (11/9/2011)

    So when a NC index is created, its leaf pages match the order of the data pages

    No, no, no. Nothing whatsoever to do with the data pages. When an index is created SQL tries to write the index's leaf pages out in the order of the index key columns.

    Fragmentation happens when the order of the index pages don't match the order of the data pages?

    Fragmentation happens when the leaf pages of an index don't match the key order of that index.

    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

Viewing 15 posts - 1 through 15 (of 51 total)

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