Paul Randal (11/26/2013)
I wish people would stop perpetuating the phrases 'external fragmentation' and 'internal fragmentation' and use the phrases that the SQL team, the tools, and Books Online uses: logical fragmentation and low page density.
None of the tools report 'external' or 'internal' fragmentation and haven't done since at last SQL Server 7.0/2000 with my old DBCC SHOWCONTIG.
I completely agree that "internal" and "external" fragmentation are useless phrases, particularly when you also start talking at a filesystem level or beyond. Perhaps we can come up with a descriptive list of various kinds of fragmentation, and then assign short names?
I'll start, and include some perhaps theoretical types. Paul, if you think this is of any value, I would more than welcome your corrections and input - I'm leaving the SQL types at the very bottom fairly empty of detail, for real experts to fill in.
****** The first set are rare, interesting, and/or often hidden types requiring less common techniques or tools to detect and/or correct - skip to the next set of asterisks for the more common types ******
"short name" - Block level fragmentation - a logical storage object comprised of noncontiguous physical blocks that could be more contiguous than they are. SSD's almost always have this. Spindle disks that have done bad sector remapping may have this.
"short name" - SAN/Host MetaLUN/virtual disk level fragmentation - a MetaLUN/virtual disk comprised of noncontiguous LUN/virtual disks that could be more contiguous than they are.
"short name" - SAN/Host LUN/virtual disk level fragmentation - a LUN/virtual disk comprised of sections of spindle/SSD space that could be more contiguous than they are. Also includes JBOD (Just a Bunch of Disks) where a partition could be more contiguous than it is. Thin provisioned virtual disks usually end up with this (for Hyper-V, the guest sees it as SAN/Host level, while the host sees it as Filesystem level fragmentation, unless you have nested hosts, in which case Google "recursion")
"short name" - Partition level fragmentation - a partition comprised of sections of LUN/spindle/SSD space that could be more contiguous than they are. Windows Dynamic disk volumes can have this level, for instance if they end up with noncontiguous or out of order extents (i.e. extents 1, 2, 3, 4, 5 and D: has extents 3,1,4 while E: has extents 2, 5)
****** Below this line are common types a systems DBA deals with ******
"short name" - Filesystem drive letter/mount point level fragmentation - A drive letter/mount point containing of multiple files that get accessed at overlapping times. For instance, a SQL Server filegroup with more than one file per drive letter/mount point, such that when SQL accesses the filegroup, reads and writes must skip back and forth between files - thus, if the drive letter/mount point represents, say, a single spindle, the drive head has to move from File A to File B and back to File A during the filegroup access. This is also caused by putting both the data and the log files of a READ_WRITE database on the same drive letter/mount point. This is reduced by having one file per drive letter/mount point that is accessed at a time.
"short name" - Filesystem File level fragmentation - one or more files comprised of sectors that could be more contiguous than they are. Typically can be reduced by filesystem level defragmentation tools (Piriform Defraggler, Windows Defrag, etc.) or backing up the file(s), deleting as much as possible including the file(s) affected, then recopying the file(s) back, single-threaded, with a tool that pre-allocated space as much as possible for the filesystem involved. SQL Server data and log files which grow are often subject to this, as they are allocated space from effectively arbitrary locations on the filesystem.
"SQL Server low page density" - pages are could be more full than they are. Typically can be reduced by index REBUILD (where it is controlled by FILLFACTOR and PAD_INDEX settings).
"SQL Server logical fragmentation" - pages and/or extents are out of order. Reduced by index REBUILD or REORGANIZE.