sql server Fragmentation:

Fragmentation:

Storing data non-contiguously on disk or page is known as fragmentation.

2 types:

1. Internal fragmentation

2. Extrenal fragmentation

1. Internal fragmentation: When records are stored non-contiguously inside the page, then it is called internal fragmentation.

This fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements

2. Extrenal fragmentation: 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.

How to find fragmentation:

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), null, NULL, null, 'DETAILED')

AVG_fragementation_percentag--column gets verified with value

Or upto SQL Server 2000 version, DBCC SHOWCONTIG----Scan Density [Best Count:Actual Count]

If AVG_fragementation_percentage <5::::Indexes are good

AVG_fragementation_percentage >5 and <30 ::::Indexes reorganize

[Data reorganize happens internally if we perform reorganize process]

AVG_fragementation_percentage >30 ::::Indexes rebuild

[ If we rebuild then existing indexes will be droped and create new indexes]

Maintaince activity: Rebuild and reorganize

Rebuild:

The existing indexes gets dropped and recreates the new indexes

ALTER INDEX indexname ON tablename REBUILD;

reorganize:

Internally data gets rearranged but no impact to existing indexes.

ALTER INDEX indexname ON tablename reorganize;

Note: Upto SQL Server 2000 version rebuild or reorganize is a offline operation ..i.e your table not accessable while rebuild is in progress to the users.

From SQL Server 2005 , Rebuild or reorganize are online operations.