Page split with Clustered index

Question

Page split with Clustered index

Viewing 7 posts - 16 through 21 (of 21 total)

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

I can buy the fact that SQL Server might call each new page allocation a "split" even though it really isn't a page split in the true sense (i.e. an insert to a clustered index requiring relocation of half of the rows on the page to a new page) that would result in fragmentation of the table.

The allocation and index updating mechanisms probably share similar code, but the insert that actually results in relocating existing rows is much more costly.

The probability of survival is inversely proportional to the angle of arrival.

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 2

sturner (7/14/2010)
I can buy the fact that SQL Server might call each new page allocation a "split" even though it really isn't a page split in the true sense (i.e. an insert to a clustered index requiring relocation of half of the rows on the page to a new page) that would result in fragmentation of the table.
The allocation and index updating mechanisms probably share similar code, but the insert that actually results in relocating existing rows is much more costly.

I edited just after you posted, but I agree with you here - similar code, but it's not really correct to call a new allocation a page split. Question then becomes, how do you track the costly page splits while ignoring the others?

RakeshRSingh SSCarpal Tunnel Points: 4109 More actions · Answer 3

Thanks a lot for your views. I really appreciate your involvement with the topic.

Hi Jim,

I got exactly the same results you have posted with clustered index. One more thing when I ran sys.dm_db_index_physical_stats DMVs I got 0.7 Avg_fragment_in_percent.

Hi Sturner,

Page Splits/Sec and Page Allocated/sec are always same, and I wish I could agree with you, because I am getting page split event in transaction log also( As Jim said) . Please note Avg_fragment_in_percent value for the table after Insert operation is 0.7.

Hi Steve,

I am getting less fragmentation after inserting 1 lakh records as mentioned above. But the point is why I am getting page split value equal to the allocated page.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

I can't find it documented, but perhaps an allocation is counting as a split for some reason. I'll see if I can get a back channel response.

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

I know this thread is over 5 years old but I believe I found out why page splits occur even in the case of ever increasing values during inserts. Please see the following. According to the article, new page allocations are registered as a "page split" although you can (apparently) separate the good kind from the bad kind.

http://www.sqlballs.com/2012/08/how-to-find-bad-page-splits.html

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

imct1960 SSC Enthusiast Points: 109 More actions · Answer 6

Thanks Jeff, I was reading the article today and was hoping for a conclusion.

It does annoy me, the accusations of thread resurrection. If the new info. is meaningful then surely that is a good thing.

Michael

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

imct1960 (1/8/2016)
Thanks Jeff, I was reading the article today and was hoping for a conclusion.
It does annoy me, the accusations of thread resurrection. If the new info. is meaningful then surely that is a good thing.
Michael

Thanks for the feedback, Michael. And, yes, I whole heartedly agree about the usually unnecessary chatter about an old thread being resurrected. If someone finds the thread and uses the info, it doesn't matter how old it is.

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