Fragmentation

Fragmentation is the storing of data non-contiguously on disk.

Detecting Fragmentation

The level of fragmentation can be determined by running the Dynamic Management View (DMV): sys.dm_db_index_physical_stats.
To return information for all databases detailing all statistics for all tables and indexes within the instance of SQL Server run

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);

A couple of the DMV columns are defined below

avg_fragmentation_in_percent: Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.. The percent of logical fragmentation (out-of-order pages in the index). The lower this value, the better it is. If this value is higher than 10%, some corrective action should be taken.

avg_page_space_used_in_percent : Average percentage of available data storage space used in all pages. Higher the value, the better it is. A value is lower than 75%, some corrective action should be taken.

Reducing Fragmentation

After the degree of fragmentation is identified from sys.dm_db_index_physical_stats, a guideline on the course of action to be taken is listed below

avg fragmentation in percent	Suggested Action
> 5%	No action required
> 5% and < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON) can be executed online or offline. A replacement for DBCC DBREINDEX