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

« SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – The Clustered Index – Simple Understanding – Day 19 of 35

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – All about SQL Statistics – Day 21 of 35 »

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

August 20, 2011 by pinaldave

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.

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.

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.

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

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.

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.

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.

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.

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.

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?

When records from one memory page are moved to another page during changes to your table.
When records from one memory page are collapsed into fewer pages from excessive deletes.
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 http://blog.sqlauthority.com which is next day GTM+2.5.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

Posted in Joes 2 Pros, Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 75 Comments

75 Responses

on August 20, 2011 at 7:45 am | Reply Rene Alberto Castro Velasquez

Correct answer is No. 1.
Page splits happen when records from one memory page are moved to another page during changes to your table.
Rene Castro
El Salvador
on August 20, 2011 at 8:04 am | Reply levpius

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

As defined in this blog

Leo Pius
USA
on August 20, 2011 at 8:07 am | Reply Dawn Hoffmann

Q: When do page splits happen?

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

Reason: When there is not enough room in the page for the new record and last record must be moved into the next page.

Dawn
USA
on August 20, 2011 at 8:12 am | Reply Santosh.S

The correct answere is option 1)

Page splits are performed when there is no space in a data page to accommodate more inserts or updates. In a page spilt, SQL server removes some data from the current data page and moves it to another data page. This is done in order to make space for the new rows.

Thanks & Regards
Santosh.S
Bangalore, India
on August 20, 2011 at 8:28 am | Reply Abhishek Mishra

Answer 1
When records from one memory page are moved to another page during changes to your table.
Abhishek Mishra
INDIA NOIDA
on August 20, 2011 at 8:46 am | Reply Ahiri vinod

Answer is

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

Country : India[Ahmedabad]
on August 20, 2011 at 9:00 am | Reply Dhruval Vikram Shah

Correct answer is option : 1

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

Thanks,
Dhruval Shah – India
on August 20, 2011 at 9:01 am | Reply Deva Rajan

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

A page is 8Kb of data which can be related to an index, data, lob’s etc. When we INSERT data in a table, they go on a page (into slots). Based on row length, a number of rows can be stored on a 8kb page. If the row length increases (longer data value), SQL Server will move the other rows in the page to accommodate the change. If the page turns out to be small for all these rows, then SQL Server grabs another new page and moves rows to the left/right of the modification onto it. This is termed as a page split.

S.Devarajan (India)
on August 20, 2011 at 9:02 am | Reply Kaushik Modi

HI all,

When do page splits happen?
Correct ans is.
1. When records from one memory page are moved to another page during changes to your table.

when recorders Record Getting update ..

I am from India.
on August 20, 2011 at 9:07 am | Reply Rajneesh Verma

Hi,
As per description “Page splits arise when records from one memory page are moved to another page during changes to your table.” It certainly says that Option 1 is the correct answer.

Thanks…
Rajneesh Verma
(INDIA)
on August 20, 2011 at 9:13 am | Reply Ritesh Shah

right answer should be option # 1:

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

BTW, I have explained little bit about page split in my article

http://www.sqlhub.com/2011/07/index-fill-factor-in-sql-server.html

but I 1000% agree the explanation that the description given in this article is much much clear to the reader due to easily given concept and images.

Ritesh (Ahmedabad, India)
on August 20, 2011 at 9:32 am | Reply manasdash

ans is option 1
on August 20, 2011 at 9:34 am | Reply SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

[...] Q 20) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Page Split – D… [...]
on August 20, 2011 at 9:34 am | Reply Manoj Sahoo

Option 1 is the correct answer

Manoj Sahoo

India
on August 20, 2011 at 9:54 am | Reply Ramakrishnan RS

Hi,

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

Point to be noted:
Page split occurs not only on INSERT operations, but also on DELETE and UPDATE operations (assuming Primary Key/ Key on which clustered index has been created is updated.) Hence, usage of the word “changes” is most appropriate.

Ramakrishnan RS
Mysore, India
on August 20, 2011 at 9:59 am | Reply Azhar Iqbal

Option 1 is Correct.
Azhar Iqbal
From Pakistan.
on August 20, 2011 at 10:00 am | Reply Shekhar Bahuguna

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

Thanks
Shekhar Bahuguna
Delhi India
on August 20, 2011 at 10:01 am | Reply Sunil (India)

Answer is Option # 1

When records from one memory page are moved to another page during changes to your table.
on August 20, 2011 at 10:14 am | Reply Shalini.M

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

ShaliniMeyyappan
India
on August 20, 2011 at 10:40 am | Reply Gopalakrishnan Arthanarisamy

Correct Answer is # 1.

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

Page splits arise when records from one memory page are moved to another page during changes to your table.

Gopalakrishnan Arthanarisamy
Unisys, Bangalore, India
on August 20, 2011 at 10:54 am | Reply Narendra

The Correct Answer is Option -1

The Page Splitting happen when the records are moving from one memory page to another memory page.

Thanks,
Narendra (India).
on August 20, 2011 at 11:15 am | Reply Ritesh Choksi

coreect answer is
When records from one memory page are moved to another page during changes to your table.
India
on August 20, 2011 at 11:26 am | Reply Shatrughna Kumar

Correct Answer is option #1.
1. When records from one memory page are moved to another page during changes to your table.

New Delhi
India
on August 20, 2011 at 11:27 am | Reply Partha Pratim Dinda

Ans is 1.

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

Partha
India
on August 20, 2011 at 11:52 am | Reply sreeram

Answer : 1

As per description “Page splits arise when records from one memory page are moved to another page during changes to your table.

Regards
Sreeram
Indian
on August 20, 2011 at 12:18 pm | Reply Karan

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

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.
So when first memory page is now full the new inertted ecord will be placed in second memory page causing page split.

Karan,
India
on August 20, 2011 at 12:22 pm | Reply Mohd Thoufeek

The Correct Answer Is # 1

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

Regards
Mohd Thoufeek
Chennai – india
on August 20, 2011 at 12:29 pm | Reply Malay Shah

The correct answere is option 1)

Page splits are performed when there is no space in a data page to accommodate more inserts or updates. In a page spilt, SQL server removes some data from the current data page and moves it to another data page. This is done in order to make space for the new rows.

Thanks & Regards
Malay Shah
Ahmedabad, India
on August 20, 2011 at 12:31 pm | Reply Shilpa Sharma

Correct Opyion is 1.

Shilpa
India
on August 20, 2011 at 12:43 pm | Reply Pankaj

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

As we know that every page has its memory so ,when memory is full in one page and trying to insert the record which exceeds memory of page, new memory allocate to the new page.

Pankaj Patil
(INDIA
on August 20, 2011 at 12:59 pm | Reply sravan

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

As defined in this blog

I’m from INDIA
on August 20, 2011 at 1:05 pm | Reply Pritesh Mehta

ANS 1.

Pritesh
India
on August 20, 2011 at 1:13 pm | Reply Ramesh

Option 1 is the correct answer.

1) When records from one memory page are moved to another page during changes to your table.
on August 20, 2011 at 1:37 pm | Reply gvprabu

Correct Answer is (1) : When records from one memory page are moved to another page during changes to your table.

Venkateshprabu || Bangalore || India
on August 20, 2011 at 1:40 pm | Reply Jatin Dutta

Answere 20 : Correct option is 1

i.e. When the table is modified the records will be updated and the memory increases. So , one memory page can conatin only 8K of data , the next record will be inserted into the new memory page.

Jatin Dutta

Country: India
on August 20, 2011 at 1:43 pm | Reply Manjula T

The correct answer is:

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

Explanation:

Page splits happen when records from one memory page are moved to another page during changes to your table.The changes to the table includes the deletes and the inserts.

Country:

India
on August 20, 2011 at 2:26 pm | Reply Monalisa Das

When there is no space in a data page to accommodate any more inserts or updates, SQL server removes some data from the current data page and moves it to another data page in order to make space for the new rows. This process is nothing but termed as Page Split in Sql Server. So, Option # 1 is CORRECT, ie. “When records from one memory page are moved to another page during changes to your table.”
on August 20, 2011 at 2:30 pm | Reply Aditya Bisoi (@AdityaBisoi07)

Question 20
Ans : When records from one memory page are moved to another page during changes to your table.

Chennai, INDIA
on August 20, 2011 at 2:44 pm | Reply Monalisa Das

When there is no space in a data page to accommodate any more inserts or updates, SQL server removes some data from the current data page and moves it to another data page in order to make space for the new rows. This process is nothing but termed as Page Split in Sql Server. So, Option # 1 is CORRECT, ie. “When records from one memory page are moved to another page during changes to your table.”

Country = INDIA
on August 20, 2011 at 2:53 pm | Reply Pratik Raval

Correct answer is option 1

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

Pratik Raval
India
on August 20, 2011 at 3:22 pm | Reply shail

correct answer is option #1

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

shail

India
on August 20, 2011 at 3:54 pm | Reply jagdish

ans is option#1

INDIA
on August 20, 2011 at 4:20 pm | Reply Deb

Answer is #1

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

This happens when a page is full and an inserted record belongs between two existing records. This causes some records on page X to need to be shifted downward and to another page so that the newly inserted record will fit where it belongs in the clustered index sequence

Deb – USA
on August 20, 2011 at 6:28 pm | Reply Sudhir Chawla

Hi,

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

Thanks

Sudhir Chawla
New Delhi, India
on August 20, 2011 at 6:48 pm | Reply premchand

Hi All

I want to ask one question.

I have one table Emp and Field is EmpId and EmpName
If I create non-clustered index on empid then will my query perform well.

with regds
Premchand
on August 20, 2011 at 8:01 pm | Reply Gordon Kane

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

Gordon Kane
USA
on August 20, 2011 at 8:12 pm | Reply dilipkumarjena

The Correct answer for the above question is Option #1.

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

This is the correct answer.

Explanation:

what is a page split?

“Page Splits” signals the number page splits for data pages, clustered index pages, or non clustered index pages because there was not enough room for a new row(data inserted).

Explanation:

When a data row is inserted into the table with a clustered index, the row must be placed in physical order according to the key value (SSN as Explained) .

Index rows must also be placed in physical order on the pages. If there is not enough room on a page for a new row,SQL (Adaptive) Server splits the page, allocates a new page, and moves some rows to the new page.

Page splitting incurs overhead because it involves updating the parent index page and the page pointers on the adjoining pages and adds lock contention. For clustered indexes, page splitting also requires updating all nonclustered indexes that point to the rows on the new page.

Other Reasons why page split happens:

• A page split occurs when there is no enough space left to perform an INSERT in the available memory/space.
• In this case the SQL Server also needs to updae the indexes related to the queries depending on the page splits.
• The SQL Servers performance is affected if there are a lot of page splits.

The factors that affect the number of page splits are:
1. Number of users
2. Level of user activity
3. The frequency of the rebuild of indexes
4. The primary key being a clustered index
5. The performance of your I/O subsystem
6. Read or write operations
7. The fill-factor used in table indexes
• Performance Monitor can be used to watch the SQLServer:Access Methods:Page Splits/sec counter.
• Solution to reduce the number of page splits:
1. Increase the fillfactor on your indexes.
2. Rebuild your indexes more often.
3. Add clustered indexes to your monotonically increasing primary keys.
4. Get a faster I/O subsystem.

Dilip Kumar Jena
Country : INDIA
on August 20, 2011 at 8:39 pm | Reply Sale A. A.

Page splits arise when records from one memory page are moved to another page during changes to your table. The correct option is 1.

(Sale, Nigeria)
on August 20, 2011 at 9:16 pm | Reply Kalyanasundaram.K

Answer : Option 1

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

Chennai, TamilNadu, India
on August 20, 2011 at 9:46 pm | Reply Basavaraj

Correct Answer is: Option 1

Thanks,
Basavaraj
India
on August 20, 2011 at 10:29 pm | Reply Kulwant kumar

Correct Answer is: Option 1

Thanks,
kulwant
Delhi
India
on August 20, 2011 at 10:32 pm | Reply vinay (@vinayprasadv)

Answer is :

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

Vinay
Pune, India
on August 20, 2011 at 11:25 pm | Reply Shiv Rampal

Answer is Option 1 –>
1.When records from one memory page are moved to another page during changes to your table.

Shiv Rampal
Indianapolis, USA
on August 20, 2011 at 11:35 pm | Reply Partha Dutta Gupta

The correct answer is both 1) and 2).
Page splits arise when records from one memory page are moved to another page during changes in the table. Due to excessive delete record may need to be shifted to previous page ,as free space may arise in previous pages. For example suppose there are 10 records in Marketing_lead table with having ID 1 to 10 . Assuming 4 record in each memory page there will be 3 memory pages.
Now, if ID 3,6,7,8,9,10 is deleted then there will be vacant space in the first page containing record with ID 1,2,and 4. And the second page record with ID 5 will be shifted to the first page.

Country –> INDIA
on August 21, 2011 at 12:41 am | Reply shekhar gurav

Correct answer : option No : 1

shekhar gurav.
country: india
on August 21, 2011 at 2:26 am | Reply Dan Quinones

#1

Page splits occur when records from one memory page are moved to another page during table changes

Dan
NJ, USA
on August 21, 2011 at 3:54 am | Reply Don

Page splits happen:

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

Country: United States
on August 21, 2011 at 4:05 am | Reply Bill Pepping

Hi Pinal,

Challenge:
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.

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

Explanation:
A Page Split occurs when a page in memory is full and a record on that memory page must be moved to another memory page to make room for a new record that has a primary key (clustered index) value less than the value of the record to be moved (presuming your clustered index is sorted in ascending order).

Country:
United States

Thanks for the knowledge!

Regards,

Bill Pepping
on August 21, 2011 at 4:51 am | Reply Garry Bargsley

I think the answer is #1.
Garry Bargsley
Arlington, TX, USA
on August 21, 2011 at 7:27 am | Reply Naomi

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

I am from USA
on August 21, 2011 at 7:29 am | Reply Naomi

@premchand

If your EmpID field is an identity field, then it will make most sense to make this index to be clustered for the best performance, in my opinion
on August 21, 2011 at 12:02 pm | Reply Nikhil Mahajan

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
on August 21, 2011 at 4:31 pm | Reply Damodaran Venkatesan

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.
on August 21, 2011 at 8:01 pm | Reply Sudeepta Ganguly

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

Sudeepta,
India.
on August 22, 2011 at 2:22 am | Reply Matt Nelson

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
on August 22, 2011 at 10:05 am | Reply Rajneesh Verma

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)
on August 22, 2011 at 11:13 am | Reply sanket

correct answer : option 11

Sanket K
mumbai (India)
on August 22, 2011 at 11:38 am | Reply Rajesh Mohanrangan

Correct Ans:1

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

Rajesh
From India
on August 22, 2011 at 12:10 pm | Reply madhivanan

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

Country : India
on August 22, 2011 at 2:28 pm | Reply Anish Shenoy.P

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
on August 23, 2011 at 9:45 am | Reply Bulent Gucuk

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
on August 23, 2011 at 11:11 am | Reply pinaldave

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.
on August 29, 2011 at 7:02 am | Reply SQL SERVER – SSQL Architecture Basics – Core Architecture Concepts – Book Available for SQL Server Certification Journey to SQLAuthority

[...] Q 20) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Page Split – D… [...]
on September 1, 2011 at 3:02 am | Reply Uday Bhoopalam

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
on August 8, 2012 at 7:02 am | Reply SQL SERVER – Clustered Index and Primary Key – Contest Win Joes 2 Pros Combo (USD 198) – Day 3 of 5 « SQL Server Journey with SQL Authority

[...] SQL Joes 2 Pros Development Series – Introduction to Page Split [...]