Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

index fragmentation. Expand / Collapse
Author
Message
Posted Friday, June 8, 2012 5:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 5, 2012 8:14 AM
Points: 34, Visits: 148
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 ?



Post #1313060
Posted Friday, June 8, 2012 5:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:15 PM
Points: 43,017, Visits: 36,179
Internal fragmentation being?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1313064
Posted Friday, June 8, 2012 7:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:24 AM
Points: 209, Visits: 571
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
Post #1313123
Posted Friday, June 8, 2012 7:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:24 AM
Points: 209, Visits: 571
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.
Post #1313126
Posted Friday, June 8, 2012 7:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,285, Visits: 781
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
Post #1313127
Posted Friday, June 8, 2012 7:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,285, Visits: 781
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
Post #1313130
Posted Friday, June 8, 2012 7:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1313135
Posted Tuesday, June 12, 2012 3:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 5, 2012 8:14 AM
Points: 34, Visits: 148
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 ?
Post #1314291
Posted Tuesday, June 12, 2012 4:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1314313
Posted Thursday, June 14, 2012 12:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 5, 2012 8:14 AM
Points: 34, Visits: 148
Thanks !
Post #1315627
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse