Fragmentation

Fragmentation

Indexes are a great thing for relational database performance. Fragmentation done when the order of pages in an index doesn’t match to the physical order in data file.  When index is created then it doesn’t require any fragmentation but as and when the data is inserted or deleted or updated, fragmentation should be done.   There are two type of fragmentation.

Internal Fragmentation

            Internal fragmentation occurs when data pages have extra spaces. If a data is having size under SQL Server’s page size, the engine will allocate page to store data. If size exceeds the page size than engine will allocate multiple pages to that data. Delete statement creates major fragmentation by leaving empty space in data pages. For Example, if a table is having 2500 pages which is 100 percent full and index is not fragmented. Table is used frequently and page size exceeds to 3000 pages that are only 83 percent full. Engine will need 13% more work on processing for 3000 pages instead of 2500 pages.

Sparsely populated data pages also increase SQL Server’s cache size, as caching happens at the page level.

External Fragmentation

            External fragmentation is done when pages are totally filled. If any insert or update query is fired which has excess size then the leaf nodes are already filled then more space is required. Then engine performs page split operation where it creates new page by splitting existing page to 50% which causes logical fragmentation because the page is not contiguous to the original. External fragmentation results in increased random I/O, where engine needs to read data from many places rather than just reading the data.

To avoid index fragmentation:

1. Choose a cluster key that complements the table’s insert pattern
2. Do not insert records with random key values
3. Do not update records to make them longer
4. Do not update index key columns
5. Implement index fill factors

SQL Server Engine uses fill factor only when an index is created, rebuild or reorganized. The index fill factor is not used during regular inserts, updates and deletes. In fact, that does not make any sense, because the whole point is to allow inserts and updated to happen and to add more records without filling up the page.

Probably the easiest way to set the fill factor is to use the FILLFACTOR option, when you create or rebuild an index. You can also use the Object Explorer to set the fill factor. Note that you cannot set fill factor when you reorganizing an index.

You can use below scrip to rebuild index with fillfactor:

ALTER INDEX [key] ON [schema].[table name] REBUILD WITH (FILLFACTOR = 90);

OR

Ø  Select Database-> Table right click on table on which you want to specify fillfactor.

Ø  In ‘Select a page’ tab, select Options.

Ø  

Removing Fragmentation

            You can rebuild or reorganize index as and when require. If you rebuild index,

-          It is faster for heavily fragmented data

-          It can reduce logging by switching to BULK_LOGGED recovery mode

-          It can also set FILLFACTOR.

-          It can use multiple CPUs

-          Must build new index before dropping old one.

-          Will rebuild indexes and update index statistics.

ALTER INDEX [key] ON [schema].[table name] REBUILD

If you reconfigure index:

-          It is incorruptible with no loss of work.

-          Faster for lightly fragmented index.

-          Will reorganize indexes but not update index statistics.

ALTER INDEX [key] ON [schema].[table name] REORGANIZE

There are some chances that statistics are not updated then unhindered data is fragmented well.  For that you need to update column statistics. When you rebuild any index on table than index statistics are also updated but for table column statistics you need to use ‘Update statistics’ statement with ‘fullscan’ option.

UPDATE STATISTICS [schema].[table name] WITH FULLSCAN