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)
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)
