An Extent has 8 physically contiguous pages. A page has 8 KB.
Fragmentation alone is not a sufficient reason to reorganize or rebuild an index. The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. This causes slower response times. If the query workload on a fragmented table or index does not involve scans, because the workload is primarily singleton lookups, removing fragmentation may have no effect.The above diagram shows the logical defragmentation. When you have index scan, you should run the alter index ... reorganize; or other statements.
USE AdventureWorks;
GO
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
- avg_fragmentation_in_percent :The percent of logical fragmentation (out-of-order pages in the index).
- fragment_count:The number of fragments (physically consecutive leaf pages) in the index.
- avg_fragment_size_in_pages:Average number of pages in one fragment in an index.
* Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.
USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'Production.Product'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON
a.object_id = b.object_id
AND a.index_id = b.index_id;
ALTER INDEX AK_Product_Name ON Production.Product REBUILD WITH (ONLINE=ON);
rerun the script with sys.dm_db_index_physical_stats
The avg_fragmentation_in_percent should be as close to zero as possible. However, the Production.Product table is so small that the table and its indexes use one extent. The avg_fragmentation_in_percent is meaningless for small data.
The "ALTER INDEX index_name ON tableobject WITH REBUILD " is a replacement for DBCC DBREINDEX. CREATE INDEX with (DROP_EXISTING=ON) has the same functionality.
The "ALTER INDEX index_name ON tableobject WITH REORGANIZE" is a replacement for DBCC INDEXDEFRAG. It reorders the leaf level pages of an index. It is an online operation.
DROP and re-create an index--you can specify the FILLFACTOR. It is offline process.
