What's a page split?

Question

Post reply

What's a page split?

Steve Jones - SSC Editor

SSC Guru

Points: 736063
More actions
December 10, 2019 at 12:00 am

#3700789

Comments posted to this topic are about the item What's a page split?

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

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

MilesC Ten Centuries Points: 1078 More actions · Answer 1

Thanks Steve!! A nice easy one for a Tuesday morning.

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 2

Thanks, I thought so, but wanted to be sure newer people to the platform are aware of this.

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 3

Steve Jones - SSC Editor wrote:

Thanks, I thought so, but wanted to be sure newer people to the platform are aware of this.

+1000 to that!

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

Stewart "Arturius" Campbell SSC Guru Points: 72617 More actions · Answer 4

Nice, easy question, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

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

Ya know... I can see why some people might select the first answer. The row exceeds the 8k page and "some data is stored elsewhere". "Some rows" certainly qualifies as "some data" and "moved to a new page" certainly qualifies as "is stored elsewhere".

Because the first answer has "When a row is updated to be larger than 8kb", it's actually a wrong answer, but I can see why people would make the mistake.

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

Saurabh.D Ten Centuries Points: 1164 More actions · Answer 6

Can I check proactively that any Insert/Update will cause page split.

ThanksSaurabh.D

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 7

Saurabh.D wrote:

Can I check proactively that any Insert/Update will cause page split.

Sure...

For inserts, you need to check to see if the inserts will occur in the same order as the keys of all our indexes. I almost guarantee that the order will violate at least one index and cause page splits if you have more than one index.

For updates, it's much more difficult because you would need to check all the variable width columns to see if any of the new data was going to cause expansion.

That's just the beginning, though. You also have to check if the expansive updates will cause a page to fill to more than 100%.

That's a shedload of extra time and I'd say it's not worth it. It is possible to capture what is happening in a table with the addition of a trigger and a monitoring table, which can actually be used to see how much expansion there is by column and how many times such expansions occurred.

There's also a way to look at log file backups to find page splits with the understanding that you shouldn't use the method prior to SQL Server 2012 SP3 because of some wicked nasty side effects and the other understanding that it frequently (usually) can't tell the difference between a good and a bad page split. Jonathon Keyhayais. I don't have the link for that handy but you can google for it.

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

Saurabh.D Ten Centuries Points: 1164 More actions · Answer 8

Thanks Jeff.

I will post few of my performance issues in same thread which are probably due to fragmentation and page split (or due to GUID). Perhaps you suggestion can help me.

ThanksSaurabh.D