Fragmentation 101

Have had to explain this a number of times recently, hence ....

Inside the database -
 
SQL External fragmentation
External fragmentation is also known as logical fragmentation.
It means the index is jumbled up, is in the wrong order! Accessing the index is no longer a sequential operation.

If the index fill factor does not leave space for new leaf pages, page splits happen and external fragmentation occurs. External Fragmentation becomes more of a problem returning larger or ordered results sets.


Schedule regular index maintenance to prevent this.

SQL Internal fragmentation
Internal fragmentation is the reverse scenerio i.e there is too much free space in the index pages.
Extra reads are issued to return all the data, hence it takes longer to fulfil the I/O operation.



Look at setting index Fill Factor options and rebuild indexes to correct this.


Outside the database -

File system Fragmentation

This is caused by growth of physical data and log files.

As a DBA you manage growth manually. If you don't, the scenario described in Bad Autogrowth happens, i.e. physical fragmentation of sql server files.

Resolving this requires -

1. Resizing your data files appropriately
   and either -
2. Taking SQL offline to run defraggler or similar
   or
3. Taking SQL offline and moving the files off and back onto the drive.

In my recent experience option 3 is quickest.