April 19, 2009 at 5:13 am
What is happening if I insert new data into tables where are indexes.
I find some term "page split", that full pages is split but how?
Thanks a lot Radek
April 19, 2009 at 5:54 am
If a row has to be inserted onto a page that's full, SQL splits the page. It allocates a new page, moves half of the rows on the full page onto the new one then changes the next and previous page pointers so that the new page is correctly linked in to the existing ones.
There's a paragraph or so on it here - http://msdn.microsoft.com/en-us/library/ms177459.aspx
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
April 20, 2009 at 3:53 am
The question is answered, but for anyone interested:
There is another type of split, new to SQL2K5. Individual columns on an existing page can be 'split' onto a ROW_OVERFLOW allocation unit - see this MSDN article for details.
Interesting fact:
Page splits are never rolled back, even if the transaction which caused the page split rolls back.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 20, 2009 at 8:29 am
GilaMonster (4/19/2009)
If a row has to be inserted onto a page that's full, SQL splits the page. It allocates a new page, moves half of the rows on the full page onto the new one then changes the next and previous page pointers so that the new page is correctly linked in to the existing ones.There's a paragraph or so on it here - http://msdn.microsoft.com/en-us/library/ms177459.aspx
Ok thanks,
and what about default values? On msdn is writing this :The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.
Its mean that leaf level is fill to 100% ? Ok, and what about non leaf level ? Is too fill to 100% or less,on MSDN is nothing about that.
Thank a lot Radek
April 20, 2009 at 8:37 am
Depends whether the index has been created with PAD_INDEX on or off. If it's on, the fill factor applies to the non-leaf levels as well. If it's off, the non-leaf levels are filled almost full (enough space is left for 1 row of the maximum size that the index can have)
The default for Pad_Index is off.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply