How to handle database fragmentation?

Two Types of Fragmentation:
  • Internal Fragmentation: When records are stored non-contiguously inside the page, then it is called internal fragmentation. In other words, internal fragmentation is said to occur if there is unused space between records in a page. This fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and therefore, to the indexes defined on the table. As these modifications are not equally distributed among the rows of the table and indexes, the fullness of each page can vary over time. This unused space causes poor cache utilization and more I/O, which ultimately leads to poor query performance. You can control the internal fragmentation with the FILLFACTOR option for the leaf-level pages and with the PAD_INDEX option for the higher-level pages of the CREATE INDEX statement.
  • External Fragmentation: When on disk, the physical storage of pages and extents is not contiguous. When the extents of a table are not physically stored contiguously on disk, switching from one extent to another causes higher disk rotations, and this is called Extent Fragmentation. Index pages also maintain a logical order of pages inside the extent. Every index page is linked with previous and next page in the logical order of column data. However, because of Page Split, the pages turn into out-of-order pages. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page. This is called Logical Fragmentation.
External fragmentation mainly slows down scans, which should not be that frequent in OLTP environments; however, they are very important in the data warehousing area. Internal fragmentation is a problem in both scenarios because the table is much bigger than it would be with a sequential key.

How to estimate fragmentation?
You can check both the internal and external fragmentation with the DMF - sys.dm_db_index_physical_stats: the internal fragmentation - the avg_page_space_used_in_ percent column, and the external fragmentation - the avg_fragmentation_in_percent column.

SELECT  OBJECT_NAME(OBJECT_ID) ,
        index_id ,
        index_type_desc ,
        index_level ,
        avg_fragmentation_in_percent ,
        avg_page_space_used_in_percent ,
        page_count
FROM    sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2014'), NULL, NULL, NULL, 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

How to reduce database fragmentation?
  • Reducing Fragmentation in a Heap: To reduce the fragmentation of a heap, create a clustered index on the table. Creating the clustered index, rearrange the records in an order, and then place the pages contiguously on disk.
  • Reducing Fragmentation in an Index: There are three choices for reducing external fragmentation, and we can choose one according to the percentage of fragmentation:
    • If avg_fragmentation_in_percent > 5% and < 30%, then use ALTER INDEXREORGANIZE: This statement is replacement for DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order. As this is an online operation, the index is available while the statement is running.
    • If avg_fragmentation_in_percent > 30%, then use ALTER INDEX REBUILD: This is replacement for DBCC DBREINDEX to rebuild the index online or offline. In such case, we can also use the drop and re-create index method.
    • NOT recommended - Drop and re-create the clustered index: Re-creating a clustered index redistributes the data and results in full data pages. The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX. See more on this option from a reply from Paul Randal:
Don’t ever suggest create/drop clustered index for heap fragmentation removal, or drop/create of a clustered index for clustered index fragmentation removal. That’s almost the worst advice you can give. Both will cause all non-clustered indexes to be removed twice.

Don’t ever suggest drop/recreate nonclustered index either – it allows any constraints being enforced to be broken.

Rebuild or reorganize, using the thresholds I put in Books Online, or whatever works for you.

References:
1. http://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/)
2. Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012 (MCSA)