June 8, 2012 at 5:28 am
Hi All,
The dynamic view sys.dm_db_index_physical_stats gives us the external fragmentation value in percent. Is there any way we can find the internal fragmentaion value ? And does the index rebuild/reorg reduce internal frag too ?
June 8, 2012 at 5:37 am
Internal fragmentation being?
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 8, 2012 at 7:41 am
GilaMonster (6/8/2012)
Internal fragmentation being?
External Fragmentation: External Fragmentation happens when a dynamic memory allocation algorithm allocates some memory and a small piece is left over that cannot be effectively used. If too much external fragmentation occurs, the amount of usable memory is drastically reduced. Total memory space exists to satisfy a request, but it is not contiguous.
Internal Fragmentation: Internal fragmentation is the space wasted inside of allocated memory blocks because of restriction on the allowed sizes of allocated blocks. Allocated memory may be slightly larger than requested memory; this size difference is memory internal to a partition, but not being used
June 8, 2012 at 7:43 am
sufiyan.sarguroh (6/8/2012)
Hi All,The dynamic view sys.dm_db_index_physical_stats gives us the external fragmentation value in percent. Is there any way we can find the internal fragmentaion value ? And does the index rebuild/reorg reduce internal frag too ?
I am not sure, but as per my knowledge index rebuild will remove internal frag, as in Index rebuilding it will create new index by droping old index.
June 8, 2012 at 7:43 am
jitendra.padhiyar (6/8/2012)
GilaMonster (6/8/2012)
Internal fragmentation being?External Fragmentation: External Fragmentation happens when a dynamic memory allocation algorithm allocates some memory and a small piece is left over that cannot be effectively used. If too much external fragmentation occurs, the amount of usable memory is drastically reduced. Total memory space exists to satisfy a request, but it is not contiguous.
Internal Fragmentation: Internal fragmentation is the space wasted inside of allocated memory blocks because of restriction on the allowed sizes of allocated blocks. Allocated memory may be slightly larger than requested memory; this size difference is memory internal to a partition, but not being used
ahem
this is DISK INDEX fragmentation.... nothing to do with memory
MVDBA
June 8, 2012 at 7:48 am
and before anyone follows that up with "it's not disk fragmentation" - what i mean is , it's the fragmentation of the index within the data file (not the fragmentation of the file on the disk)
the sys.dm_db_index_physical_stats gives you information about the contiguity and wasted space in the files at both a logical and physical level.
MVDBA
June 8, 2012 at 7:51 am
jitendra.padhiyar (6/8/2012)
GilaMonster (6/8/2012)
Internal fragmentation being?External Fragmentation: External Fragmentation happens when a dynamic memory allocation algorithm allocates some memory and a small piece is left over that cannot be effectively used. If too much external fragmentation occurs, the amount of usable memory is drastically reduced. Total memory space exists to satisfy a request, but it is not contiguous.
Internal Fragmentation: Internal fragmentation is the space wasted inside of allocated memory blocks because of restriction on the allowed sizes of allocated blocks. Allocated memory may be slightly larger than requested memory; this size difference is memory internal to a partition, but not being used
Firstly that has nothing whatsoever to do with indexes or index rebuilds, second, I want the OP's definition because people use the term to mean several different things
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 12, 2012 at 3:13 am
Just to clear things , this is what my understanding is of index fragmentation :
1)internal frag - physical , happens at the leaf level , ie. index grows .
2) external frag - logical , pages grow and scanning takes longer.
Now , our sys.dm_db_index_physical_stats view gives us the external frag only , right ? in percentage ?
What about the internal frag ? How do we find it ? And does it lower down when an index is defragmented ?
June 12, 2012 at 4:11 am
No, neither definition is correct.
Typically:
Internal fragmentation: space free on pages within the index. Shown by avg_page_space_used_in_percent in sys.dm_db_index_physical_stats.
External fragmentation: pages out of order within the index (logical order doesn't match physical). Shown by avg_fragmentation_in_percent in sys.dm_db_index_physical_stats.
Rather don't use the terms 'internal fragmentation' and 'external fragmentation' as they have no precise definition and everyone means something different.
Logical fragmentation - pages out of order
Low page density - space free on pages.
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 12:14 am
Thanks !
April 25, 2013 at 1:55 am
USE EXEC dbo.sp_spaceused TO FIND OUT INTERNAL FRAGMENTATION.
April 25, 2013 at 2:13 am
sufiyan.sarguroh (6/8/2012)
And does the index rebuild/reorg reduce internal frag too ?
Yes it does.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
April 25, 2013 at 2:24 am
iamsql1 (4/25/2013)
USE EXEC dbo.sp_spaceused TO FIND OUT INTERNAL FRAGMENTATION.
that 100% categorically does not give you internal fragmentation, external fragmentation or any other kind of fragmentation.. it tells you the size of the file and how it is used
MVDBA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply