June 13, 2012 at 12:35 pm
That is... not how I learned the term Internal Fragmentation. My apologies for the confusion. Thank you for the clarifications.
The idea, however, that internal fragmentation on a page is a concern for optimization (besides having more pages than you need, thus pulling more stuff to memory) however is a bit odd to me. Fillfactors being what they are on purpose to allow for avoiding page splitting, sure, you need to clean it up occassionally, but not just for that.
Gail, I don't want to further foot in mouth at this point with descriptive lingo and I'm hoping you've got a bookmark I don't. There's a good article/blog/etc out there (I keep finding the incomplete ones) that describes how row order on a page isn't guaranteed to the order of the index, merely that they're within the page header's range within the index. The row is merely appended to the end of the page and the row pointer in the header links to that area. This would also cause 'internal fragmentation' in a way, if you wanted to go to that level. However, this is built in, expected, and is meant as an optimization.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 13, 2012 at 12:43 pm
Evil Kraig F (6/13/2012)
The idea, however, that internal fragmentation on a page is a concern for optimization (besides having more pages than you need, thus pulling more stuff to memory) however is a bit odd to me. Fillfactors being what they are on purpose to allow for avoiding page splitting, sure, you need to clean it up occassionally, but not just for that.
There's a difference between the pages being ~80% full because the fill factor is set to 80 and the pages being on average ~50% full because of page splits. The first is by choice to reduce logical fragmentation. The second is just a waste of my buffer pool memory and my disk.
There's a good article/blog/etc out there (I keep finding the incomplete ones) that describes how row order on a page isn't guaranteed to the order of the index, merely that they're within the page header's range within the index. The row is merely appended to the end of the page and the row pointer in the header links to that area.
Yup, that's mostly correct. Not necessarily appended to the end, if there's free space between the existing records (from deletes) it can be reused. But there is no 'order' of rows on a page
This would also cause 'internal fragmentation' in a way, if you wanted to go to that level. However, this is built in, expected, and is meant as an optimization.
I suppose you could call it that. Not ever seen the term apply to that before.
Another reason why I don't like the terms 'internal fragmentation' and 'external fragmentation'. Everyone's got their own definition so you have to ask for the definition of the term before having a sensible discussion.
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
June 13, 2012 at 1:00 pm
GilaMonster (6/13/2012)
There's a difference between the pages being ~80% full because the fill factor is set to 80 and the pages being on average ~50% full because of page splits. The first is by choice to reduce logical fragmentation. The second is just a waste of my buffer pool memory and my disk.
Oh, that I won't argue. However, due to page splits causing page-order fragmentation (to avoid the terminology) and needing cleanups due to that, I wasn't considering page-split dead space as part of my consideration of it. Massive deletes that aren't in a range of pages can cause a similar issue, of course, due to sporadic page removal. So, I'm not arguing your point, I'm just trying to clarify my opinion of how I look at the issue.
Yup, that's mostly correct. Not necessarily appended to the end, if there's free space between the existing records (from deletes) it can be reused. But there is no 'order' of rows on a page
Ah, good clarification, but that also depends on other factors like how much VARCHAR() space is needed and the like if memory serves. But yeah, it's not always an append.
I suppose you could call it that. Not ever seen the term apply to that before.
Another reason why I don't like the terms 'internal fragmentation' and 'external fragmentation'. Everyone's got their own definition so you have to ask for the definition of the term before having a sensible discussion.
Heck, I'm living proof. I'm still having a hard time picturing 'external fragmentation' as being anything that's internal to the mdf. Those 8 years of internal lingo is going to be hard to remove as a habit.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 13, 2012 at 1:06 pm
Oh, and one more clarification... There will never be an issue with free space being fragmented within a particular index/data page, as SQL automatically compacts pages if that is ever the case.
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
June 13, 2012 at 1:10 pm
GilaMonster (6/13/2012)
Oh, and one more clarification... There will never be an issue with free space being fragmented within a particular index/data page, as SQL automatically compacts pages if that is ever the case.
So Gail, if you had a takeaway message for free space and deletes... What would it be?
Jared
CE - Microsoft
June 13, 2012 at 1:17 pm
Monitor the avg page density and if it is too low (for whatever your definition of too low is) rebuild or reorg the index, just as you do with the avg fragmentation in percent
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
June 13, 2012 at 1:19 pm
GilaMonster (6/13/2012)
Monitor the avg page density and if it is too low (for whatever your definition of too low is) rebuild or reorg the index, just as you do with the avg fragmentation in percent
Thanks! Makes "logical" sense.
Jared
CE - Microsoft
June 14, 2012 at 9:44 am
Evil Kraig F (6/13/2012)
That is... not how I learned the term Internal Fragmentation.
Gail's point about attaining a definition from the person speaking about fragmentation is very appropriate here. I made a point to say "as it relates to the index" in my second post specifically for this reason, to be sure we're talking about index fragmentation. Fragmentation as it relates to a physical data file (mdf or ndf on the Windows file system) is a completely different area of discussion.
As an aside, I do not see many people paying attention to internal index fragmentation when maintaining indexes, but I can see where some workloads would create an area of concern.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 14, 2012 at 10:05 am
I would simply recommend staying away from the terms 'internal fragmentation' and 'external fragmentation' entirely as there is no clear definition.
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
June 14, 2012 at 10:13 am
GilaMonster (6/14/2012)
I would simply recommend staying away from the terms 'internal fragmentation' and 'external fragmentation' entirely as there is no clear definition.
In general I would tend to agree. If presented in terms of a specific area of focus however (e.g. indexes, memory and file system) I think it is quite relevant to make the distinction. Not all fragmentation is created or dealt with equally.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 14, 2012 at 10:28 am
Sure, not all types of fragmentation are equally problematic, but the terms 'internal fragmentation' and 'external fragmentation' are ambiguous enough that if I say 'Internal fragmentation is a major concern', you can't immediately assume you know what fragmentation I'm talking about, even if I say 'within an index' cause I still might mean something different to what you assume.
If I say 'Low page density is a major concern', now you know what I'm talking about and can agree or disagree without having to ask first for a definition.
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
June 14, 2012 at 10:50 am
GilaMonster (6/14/2012)
Sure, not all types of fragmentation are equally problematic, but the terms 'internal fragmentation' and 'external fragmentation' are ambiguous enough that if I say 'Internal fragmentation is a major concern', you can't immediately assume you know what fragmentation I'm talking about, even if I say 'within an index' cause I still might mean something different to what you assume.If I say 'Low page density is a major concern', now you know what I'm talking about and can agree or disagree without having to ask first for a definition.
A fair point. It's always best to ask but I do not see the terminology going away anytime soon.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply