Post reply

Understanding Page Splits

  • September 20, 2012 at 4:34 am

    #261825

    Hi All

    If we have an Index that consists of 10 pages and they are all full.

    If we insert a row that will need to insert data onto page 5, SQL Server creates a new page, takes half of the rows out of page 5 and puts it in the newly created page.

    Am I right so far?

    Also, will the newly created page by default be page 11?

    Does that newly created page go to the end of the line?

    Thanks

  • September 20, 2012 at 4:36 am

    #1539980

    Pretty much. The new page does not have to be page 11, can be any page anywhere in the filegroup.

    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

  • September 20, 2012 at 6:40 am

    #1540036

    GilaMonster (9/20/2012)

    Pretty much. The new page does not have to be page 11, can be any page anywhere in the filegroup.

    Am I right in that the non-leaf pages in the index are automatically updated as to the new location of the data?

    Thanks

  • September 20, 2012 at 7:16 am

    #1540057

    Of course.

    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

  • September 20, 2012 at 8:53 am

    #1540143

    GilaMonster (9/20/2012)

    Of course.

    Cool, thanks

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

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