SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Page Split – Day 20 of 35

Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 3.
Every day one winner from United States will get Joes 2 Pros Volume 3.

From yesterdays post we learned that the clustered index is the placement order of a table’s records in memory pages. When you insert new records, then each record will be inserted into the memory page in the order it belongs.

Rick Morelan’s SSN (555-55-5555) belongs with the 5’s, so his record will be physically inserted in memory between Jonny Dirt and Sally Smith. Is there enough room in this page to accommodate his record without having to move other record(s) to a new page? Yes there is, and afterwards the first memory page is full. If you insert a new record(s), it is inserted into the memory page in the order it belongs.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Page Split - Day 20 of 35 J2P_20_1

Next, we have another new record coming in, Vince Verhoff. His record belongs in sequence after Irene Intern, so he will begin occupying the next page of memory.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Page Split - Day 20 of 35 J2P_20_2

The clustered index is the placement order of a table’s records in memory pages. When you insert new records, then each record will be inserted into the memory page in the order it belongs.

Page Splits

Page splits arise when records from one memory page are moved to another page during changes to your table. Here we see another new record (Major Disarray) being inserted, in sequence, between Jonny and Rick. Since there’s no room in this memory page, some records will need to shift around. The page split occurs when Irene’s record moves to the second page.

Page splits are considered very bad for performance, and there are a number of techniques to reduce, or even eliminate, the risk of page splits.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Page Split - Day 20 of 35 J2P_20_3

Next we’ll see this data entered into the HumanResources.Contractor table, which contains a clustered index on the SSN field.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Page Split - Day 20 of 35 J2P_20_4

Notice from the figure above that SSN is set as a primary key. A primary key is a constraint which ensures non-nullability and uniqueness (i.e., no duplicate values) in a field. Only one primary key per table is allowed. When you create a primary key, SQL Server creates two objects:  the primary key and an index (which by default is clustered).  The data in this table will be physically ordered by SSN.  Now insert the first three records into the table.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Page Split - Day 20 of 35 J2P_20_5

Let’s use our same assumption that four records fit into a page of memory. These three records would all occupy the same memory page.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Page Split - Day 20 of 35 J2P_20_6

So that means there is room in the same memory page for an INSERT to add one additional record to this table, for a total of four records.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Page Split - Day 20 of 35 J2P_20_7

In our example, the first memory page is now full, since it contains four records. The second memory page is empty. The layout in the memory page is depicted with the fourth record (highlighted) added between Jonny Dirt and Sally Smith. The two bottom records (Sally Smith and Irene Intern) shifted down to make room for the “Rick Morelan” record to be inserted in proper sequence, according to the value of the clustered field (SSN).

The next insert (for Vince Verhoff – see figure below) goes straight into the second memory page and does not cause a page split. Now look at the SSN values in the table. Now the SSN value of the next record to be added will tell us whether there will be a page split. Any subsequent INSERT with an SSN value within the range 888-88-8889 through 999-99-9998 would go into one of the three available rows in the second memory page.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Page Split - Day 20 of 35 J2P_20_8

Any SSN value below 888-88-8888 will cause a page split. The 444-44-4444 Major Disarray value must be inserted in Page 1 between Jonny and Rick, a page split is caused and Irene Intern’s record must move to Page 2.  Notice that it won’t take long before almost every insert causes a page split.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Introduction to Page Split - Day 20 of 35 J2P_20_9

NOTE: Please note that this is Level 100 explanation and how the rows are ordered and B Trees are organized are out of the scope of this article.

NOTE1: This article is just a introduction and not a deep dive into the storage structure. Here is the MSDN page for complete reference.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLArchChapter4.2Setup.sql script from Volume 3.

Question 20

When do page splits happen?

  1. When records from one memory page are moved to another page during changes to your table.
  2. When records from one memory page are collapsed into fewer pages from excessive deletes.
  3. When you insert records in order by the clustered index and you table needs to claim more memory space.

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 3.
Winner from India will get Joes 2 Pros Volume 3.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – The Clustered Index – Simple Understanding – Day 19 of 35
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – All about SQL Statistics – Day 21 of 35

Related Posts

73 Comments. Leave new

  • Nikhil Mahajan
    August 21, 2011 12:02 pm

    Correct answer is option No : 1
    i.e.
    1) When records from one memory page are moved to another page during changes to your table.

    As you mentioned in this post

    India

    Reply
  • Damodaran Venkatesan
    August 21, 2011 4:31 pm

    The correct answer is Option 1.

    This happens due to the records moved from one memory page to another page for not having enough space to store the entire record on that page.

    Damodaran Venkatesan
    USA.

    Reply
  • Sudeepta Ganguly
    August 21, 2011 8:01 pm

    The correct answer is:
    1.When records from one memory page are moved to another page during changes to your table.

    Sudeepta,
    India.

    Reply
  • Page splits happen when #1: When records from one memory page are moved to another page during changes to your table.
    When a row is moved to a new page in order to keep the clustered index in order, a page split occur.
    Matt Nelson, USA

    Reply
  • Rajneesh Verma
    August 22, 2011 10:05 am

    Hi,
    Option 1 is the correct Answer “When records from one memory page are moved to another page during changes to your table.”

    Thanks….
    Rajneesh Verma
    (INDIA)

    Reply
  • correct answer : option 11

    Sanket K
    mumbai (India)

    Reply
  • Rajesh Mohanrangan
    August 22, 2011 11:38 am

    Correct Ans:1

    1.When records from one memory page are moved to another page during changes to your table.

    Rajesh
    From India

    Reply
  • Answer 1
    1.When records from one memory page are moved to another page during changes to your table.

    Country : India

    Reply
  • Anish Shenoy.P
    August 22, 2011 2:28 pm

    Hi Sir,

    The correct answer is option no 1.
    When records from one memory page are moved to another page during changes to your table.

    Page splits arise when there is no room for the record in the memory page, some records need to shift around.

    P.Anish Shenoy,
    INDIA, Bangalore, Karnataka

    Reply
  • Hello,

    The correct answer is option #1.

    1. 1.When records from one memory page are moved to another page during changes to your table.

    Bulent

    Reply
  • Q 20) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Page Split – Day 20 of 35

    A.) A

    Winner from USA: Dawn Hoffmann

    Winner from India: Deva Rajan

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

    Reply
  • Correct answer is 1.
    Page splits arise when records from one memory page are moved to another page during changes to your table.

    Uday
    USA

    Reply
  • it is very good post i am junior in sql

    Reply

Leave a Reply

Menu