7 responses

  1. Blaise
    2017-04-13

    Hi,

    I know I’m a little late to the party here, but it’s the most detailed article I could find on the subject.
    The web is full of info on page splits (the normal page splits and the “nasty” page splits), however, there is one question for which I cannot find a definite answer.

    Specifically : what happens if I build a DESC clusteded index with an auto-increment key ?
    In that situation, we’ll always _prepend_ to the index, not append.

    By your buddy’s definition, “a good split is when adding a new page on the right hand side of the index’s leaf level”, it is not a good split.

    However, it’s not an insert in the middle either : we are always inserting on the left hand side.

    But what about within the page itself ? Are record sorted within a page ? Does it matter ?
    Say I have 10 records ( 1 through 10) that fit in a single Page (Page 0)
    When I insert id 11, we’ll allocate a new page (Page 1) at the beginning of the index and put record 11 in it.
    Will this record be at the beginning of the new page or at the end (if there is such a concept).
    And what happens with we insert 12 in that page ?

    When the two pages are full, what do they look like ?

    Layout 1:
    Page 1: 20-19-…-12-11 Page 2: 10-9-…-2-1
    Layout 2:
    Page 1: 11-12-…-19-20 Page 2: 1-2-…-9-10

    Is it layout 1 or layout 2 (or maybe layout 3 where records have no order within a page anyway)

    Bottom line : is there any performance penalty to doing that ?

    NOTE : the reasoning for having a descending index was that newer records are typically more interesting and looked up more often than older record. And most request have a order by Id desc.

    Reply

    • Wayne Sheffield
      2017-04-13

      Hi Blaise,
      I’m glad that you like the article.

      A good page split is really when the storage engine has to add a new page to either the left or right hand side of index’s leaf level. In my example above, this was performed with the setting of the identity column. In your example, this is performed by having the index in a descending order. In either case, the new pages will be allocated on the left side.

      Note that with either method, adding new pages to the left side will cause fragmented indexes.

      As far as the order of the rows on the page: All of the rows that have to be on the page (due to the values in the columns in the index key) will be on that page. The slot array on that page will be maintained in the proper order for the rows that are on the page. However, the data for that row may or may not physically be in that order. See my posts Does a Clustered Index really physically store the rows in key order? and Automating DBCC PAGE – Part 3 (Is a Clustered Index physically sorted on disk?) for a more detailed explanation of how this works.

      Reply

  2. Blaise
    2017-04-13

    Messed up the page numbering. Should have been :

    Layout 1:
    Page 1: 20-19-…-12-11 Page 0: 10-9-…-2-1
    Layout 2:
    Page 1: 11-12-…-19-20 Page 0: 1-2-…-9-10

    Reply

  3. Martin Smith
    2018-12-19

    you should be able to use ALTER TABLE switch and a drop and rename to get the existing data into a table with the desired updated identity specification without any data movement.

    Reply

    • Wayne Sheffield
      2018-12-20

      Interesting idea. I’ll have to try this out.

      Reply

  4. Martin Smith
    2018-12-19

    As well as the effects of page splits there is also the issue of logical fragmentation. If the pages are allocated in ascending page order and the ids in descending numeric order you will end up with a perfectly fragmented index.

    Reply

Leave a Reply to let me know how you liked this post

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to top
mobile desktop