Page Splitting

Question

Page Splitting

subha.mcts2005

SSC Eights!

Points: 928
More actions
September 17, 2010 at 7:27 am

#148697

Hi Experts,
what is Page splitting in SQL server?
Subhash
DBA

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://www.google.com/search?q=%22page+split%22+%22SQL+Server%22

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

subha.mcts2005 SSC Eights! Points: 928 More actions · Answer 2

Thanks gail,

but plz give me brief Description........

hallidayd SSCrazy Points: 2490 More actions · Answer 3

Out of curiosity, why do you expect Gail to write something off the cuff that would be better than the articles she referenced? She is very good (I know her from another forum) but she will not likely be giving you any better information than you could find yourself.

Why not have a read of one of the referenced articles and come back if there's any specific thing that confuses you.

Oliiii SSCertifiable Points: 5328 More actions · Answer 4

subha.mcts2005 (9/17/2010)
Thanks gail,
but plz give me brief Description........

SQL Server want to write data at a specific place (because of an index), but if there is not enough room in the current page it has to split it in two.

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 5

Give us the exact wording of your error message.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

Hi Subha,

From what I remember...

Fill Factor of an index indicates 'how much percent of a page SQL Server can Fill'. For example, if the Fill Factor is 100% then SQL Server will fill the index page completely without leaving any free space.

Suppose that if a page is 100% full and if a new record record needs to be inserted somewhere in-between the records on that page, then SQL Server will break the page into 2 pages and spreads the data already present on the actual page between the 2 pages and then inserts the new record at its required position on the page. This is called Page splitting.

But the new page that resulted out of this page splitting mechanism, needn't be very next page to the actual page. And this results in index fragmentation.

To avoid this Index fragmentation, it is often required to specify a less Fill Factor like 50-75%.This requirement is for the tables with lot of inserts but not for the read activity.

Thank You,

Best Regards,

SQLBuddy.

hallidayd SSCrazy Points: 2490 More actions · Answer 7

sqlbuddy123 (9/17/2010)
Fill Factor of an index indicates 'how much percent of a page SQL Server can Fill'. For example, if the Fill Factor is 100% then SQL Server will fill the index page completely without leaving any free space.

Nope - fillfactor is just the percentage of each leaf level page that SQL Server will fill when rebuilding\ creating the index. Apart from during these two operations the fillfactor has no meaning.

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 8

sqlbuddy123 (9/17/2010)
Suppose that if a page is 100% full and if a new record record needs to be inserted somewhere in-between the records on that page, then SQL Server will break the page into 2 pages and spreads the data already present on the actual page between the 2 pages and then inserts the new record at its required position on the page. This is called Page splitting.

That is inaccurate. If a record needs to be inserted between two other records, and there's not enough room on the page, a new page will be created and the records will be re-arranged as necessary. That is NOT a split page.

Check Gail's google search for links. The very first one is a good article.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Brandie Tarvin (9/17/2010)
If a record needs to be inserted between two other records, and there's not enough room on the page, a new page will be created and the records will be re-arranged as necessary. That is NOT a split page.

It may or may not be a page split, depending where in the index chain that full page is. In the middle = yes, at the end = no.

Page split occurs any time SQL does not have space available on the target page of an index, where the page is not the last in the index chain.

Updates can cause page splits if they increase the size of the row, inserts can cause page splits is there isn't space on the page for the new row.

Deletes never cause page splits and page splits do not occur in a heap, only in an index.

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