How Bad are Bad Page Splits?

Question

How Bad are Bad Page Splits?

Mike Byrd

Default port

Points: 1458
More actions
November 25, 2019 at 12:00 am

#3700355

Comments posted to this topic are about the item How Bad are Bad Page Splits?
Mike Byrd

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

Nice job, Mike.

One of the biggest problems is that there are a huge number of common circumstances where lowing the FILL FACTOR will do absolutely nothing to prevent the page splits. Most of these circumstances occur when you have a large number of "Append Only" Inserts followed by (usually) "ExpAnsive" Updates. The Inserts will NOT follow the FILL FACTOR. Instead, they will fill each page to the max (approaching 100%) as the average row size will allow. When those same rows are Updated with "ExpAnsive" Updates, they're virtually guaranteed to split.

That's really, really bad and not just for Selects. Paul Randal demonstrates that a bad (he calls them "nasty") page split will take 4.5 to 43 times longer longer than a good one. Since these types of splits are all happening on the latest data and they're all very close together at the logical end of the index, you all get massive blocking for other Inserts not to mention for Selects. It also causes a shedload of extra activity on the log file and that can slow a whole lot more down than just things having to do with the given table.

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Thanks for setting up the tests and crunching the numbers, Mike!

I just wanted to take issue with this (emphasis mine):

Data can be added row by row to a page until the specified fill factor is reached. When that is reached the next row inserted must go to a new page.

Shouldn't that say "until the page is full"? My understanding is that the purpose of specifying a fill factor is to allow empty space for new rows to be written into, and fill factor is only ever observed when the index is built or rebuilt.

John

BobAtDBS SSCarpal Tunnel Points: 4590 More actions · Answer 3

Loved the analysis, one thing bothered me. Either I have a gross misunderstanding or the author said something incorrectly:

Fill factor is defined as the maximum percent of the fullness of an index page of data. Data can be added row by row to a page until the specified fill factor is reached. When that is reached the next row inserted must go to a new page.

I thought the whole purpose of a fill factor of less than 100% was to allow the pages to "fill up" after a reorg so that you didn't get page splits immediately? If I reorg to 80%, what do I gain when the next row is added if I can't go to 81%?

Student of SQL and Golf, Master of Neither

Mike Byrd Default port Points: 1458 More actions · Answer 4

All of ya’lls comments are absolutely correct. Not sure where my brain was when I wrote the original paragraph, but obviously not in this universe. This is what I should have said:

“Let’s discuss what constitutes a good page split and a bad page split. Data can be added row by row to a page until it is filled (there is not enough space within the page for another row to be added). When there is not enough space within the page a new page is added to the index. Normally it is the next page in the extent, but if the extent is filled then a new extent (8 pages) are allocated to the index. Fill Factor is not a player here other than it sets aside space for additional rows after an index rebuild.”

Mike Byrd

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 5

John Mitchell-245523 wrote:

Thanks for setting up the tests and crunching the numbers, Mike!
I just wanted to take issue with this (emphasis mine):
Data can be added row by row to a page until the specified fill factor is reached. When that is reached the next row inserted must go to a new page.
Shouldn't that say "until the page is full"? My understanding is that the purpose of specifying a fill factor is to allow empty space for new rows to be written into, and fill factor is only ever observed when the index is built or rebuilt.
John

I totally missed that in my admittedly quick read. What you say is 100% correct, John. There is a very rare but useful instance where that's not correct but, when it's RBAR, it's always correct.

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 6

Mike Byrd wrote:

All of ya’lls comments are absolutely correct. Not sure where my brain was when I wrote the original paragraph, but obviously not in this universe. This is what I should have said:
“Let’s discuss what constitutes a good page split and a bad page split. Data can be added row by row to a page until it is filled (there is not enough space within the page for another row to be added). When there is not enough space within the page a new page is added to the index. Normally it is the next page in the extent, but if the extent is filled then a new extent (8 pages) are allocated to the index. Fill Factor is not a player here other than it sets aside space for additional rows after an index rebuild.”

Submit a change to Steve Jones. I'm sure he'd be happy to accommodate. I also recommend that the word "are" after the "(8 pages)" be an "is" because the plural of pages is parenthetical.

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