Post reply

How do page splits work with large amounts of sequential data inserted in the middle?

  • February 15, 2017 at 10:14 am

    #320371

    Let's say I have a table with a large number of records that has a clustered index on a varchar column.
    One day, I need to add a large number of sequential records that all begin with 'B'. There are no records in the table already in the middle of the new sequence of records. If I were doing this in a manual file cabinet, I could separate two files and plunk all of this down in-between.
    What happens in SQL Server? Does it split the page, fill the page, split again, etc... until it is all done? If so, are these splits essentially like the splits that would occur if I were entering at the end of the data range? Or are they like the initial split?
    Or does it somehow allocate enough pages all at once to handle the entire entry? If there's enough new data to fill 20 pages, does it create all those pages at once or one at a time? Are these "good" splits or "nasty" splits?

    --------------------------------------
    When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
    --------------------------------------
    It’s unpleasantly like being drunk.
    What’s so unpleasant about being drunk?
    You ask a glass of water. -- Douglas Adams

  • February 15, 2017 at 3:44 pm

    #1928741

    You could get an initial page split.  But after that, SQL detects that incoming values are sequential and shouldn't do another page split unless you overlap other existing rows.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • February 15, 2017 at 9:12 pm

    #1928753

    Sioban Krzywicki - Wednesday, February 15, 2017 10:14 AM

    Let's say I have a table with a large number of records that has a clustered index on a varchar column.
    One day, I need to add a large number of sequential records that all begin with 'B'. There are no records in the table already in the middle of the new sequence of records. If I were doing this in a manual file cabinet, I could separate two files and plunk all of this down in-between.
    What happens in SQL Server? Does it split the page, fill the page, split again, etc... until it is all done? If so, are these splits essentially like the splits that would occur if I were entering at the end of the data range? Or are they like the initial split?
    Or does it somehow allocate enough pages all at once to handle the entire entry? If there's enough new data to fill 20 pages, does it create all those pages at once or one at a time? Are these "good" splits or "nasty" splits?

    For the most part, Scott is spot on.  But it does depend on the width of the rows in the table and the width of the rows you're inserting. For example, if you have a page with two rows, one being over 4k and one being just under 4k and you insert a row that's over 4K, you could get two page splits out of it.  I've forgotten the exact mechanism and my example is poor but (IIRC) either Paul Randal or Kimberly Tripp showed how that can happen on the SQLSkills site.

    --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