Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 340

IdxPhyStat.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_
id(N'AdventureWorks2008R2'), NULL, NULL, NULL , 'DETAILED')
IdxPhyStat
JOIN sys.tables SysTab WITH (nolock) ON IdxPhyStat.object_id =
SysTab.object_id
JOIN sys.indexes SysIdx WITH (nolock) ON IdxPhyStat.object_id =
SysIdx.object_id AND IdxPhyStat.index_id = SysIdx.index_id
WHERE SysTab.is_ms_shipped = 0
ORDER BY TableName,avg_fragmentation_in_percent
GO

5. The Logical Fragmentation is the percentage of out-of-order pages in the
leaf pages of an index. The Extent Fragmentation is the percentage of out-of-
order extents in leaf pages of a heap (no indexes). Now, we having the information
on fragmented tables, and let us work on sustainable practices to reduce the
fragmentation and maintain the index on these tables, assuming the tables row
count is running several millions and the database is considered as a VLDB.

6. The frequency of how often to rebuild or reorg an index depends on the

database size.

7. There are three choices for reducing fragmentation: drop and recreate the clustered

index ( CREATE INDEX ), reorganize the index ( ALTER INDEX REORGANIZE ), and
rebuild the index ( ALTER INDEX REBUILD ). To reduce the fragmentation on a heap,
create a clustered index on the table and then drop the index.

As best practice, it is essential to perform REBUILD operation
if the physical fragmentation is between 15 percent and 20
percent. If the logical fragmentation is between 30 percent and
40 percent then performing REORGANIZE of indexes is ideal.

8. Perform the index reorganize job on every alternate night on all the tables that will
have frequent insert, delete, and update transactions.
9. Perform the index rebuild job on every weekend on all the tables that will have
frequent insert, delete, and update transactions.
10. Let us perform the different variations of rebuilding a specific index
PK_CreditCard_CreditCardID and/or all the indexes on a table in
AdventureWorks2008R2 database:
--******************************
-- Rebuild a specific index
ALTER INDEX PK_CreditCard_CreditCardID ON Sales.CreditCard REBUILD

-- Rebuild all indexes on a specific table
ALTER INDEX ALL ON Sales.CreditCard REBUILD