July 22, 2002 at 9:49 pm
Hi all
If I had 10 rows in a page, and around 50% of the total page space is still "free". The columns for arguement sake is a single identity with 5 other varchar(100) columns. When I update, say, row 1 of 10, I gather the update results in the allocation of free space in the same page?
My question is:
a) where is the pointer information stored, with the column ive updated??
b) from what I understand, an update resulting in no free avail space in the same page causes a page split?
c) given a), this is a type of fragmentation right? (althpough not an issue re the read-mechanics of sqlserver).
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 23, 2002 at 4:26 pm
a)Get a copy of Inside SQL Server, it will explain this complicated topic in detail.
b)yes
c)not really fragmentation to me, but an intersting idea. Not something you can do anything about unless you can control the data.
Steve Jones
July 24, 2002 at 6:58 am
For B I am not sure that would be a yes. It depends on the columns affected. If there is no free space and the column from a clustered index is not updated the the record is overwritten no movement occurrs and no page split. If the columns in a clustered index is updated then movement occurrs but if moving on the same page the old record is removed and the new insterted so no page split should occurr. If moved to another page and that page is full then a page split will occurr. But for your description of you table I don't think any need to worry as the total datasize is roughly 5kb and the size of a page is 8kb you have plenty of space not to worry. However, here is the kicker. If you have other small objects that do not use a full page then a page can be mixed. Up to 8 items I believe can share a page. So you have to take these items into account and page splits may occurr based on objects being moved out of the page. Then once a table reaches at least one page this mixing ends at the page level but still can occurr for the extent. So be is a maybe.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply