December 30, 2013 at 3:39 am
I have found a database with some very bad database design.
The one I am focussing on right now is the datatypes used for the clustered index. Multiple Varchar columns, with the left most column not very unique. P*ss poor for more than 1 reason.
My question is regarding page splitting, My understanding is that the use of varchar or GUIDs. Will force page splitting due to the column not being sequential. SQL will not know the next value so instead of creating a brand new empty page.
It will split the current page that is full over 2 pages and make them 50% full each. Causing fragmentation. The pages will not be sequntial.
Sequentially loading clustered index doesn't do this as SQL knows the next value and simply creates a new empty page with the next value at the top. Pages are kept in order no fragmentation.
Any clarification will be welcomed.
December 30, 2013 at 3:40 am
I'm sure I read an artical on this recently on SSC but I can't locate it at the moment.
December 30, 2013 at 4:10 am
FOUND THIS..
http://www.sqlskills.com/blogs/paul/can-guid-cluster-keys-cause-non-clustered-index-fragmentation/
WHich backs up my understanding.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply