Use the below query to determine the Fragmentation
percentage for the tables in a particular database.
SELECT ps.database_id, ps.OBJECT_ID,o.name,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
inner join sys.objects o
on ps.OBJECT_ID = o.OBJECT_ID
WHERE ps.database_id = DB_ID()
ORDER BY ps.avg_fragmentation_in_percent
desc
Re-organize the index if Fragmentation percent is > 5% and < 30%
Re-Build the index if Fragmentation percent is > 30%
Syntax for
Re-Organize
ALTER INDEX <<index_name>> ON <<table_name>>
REORGANIZE ;
Example:
ALTER INDEX IX_FactEmail_EventDateKeyStoreKey ON fact.email
REORGANIZE ;
Syntax for Re-Build
ALTER TABLE <<table_name>> REBUILD
Example:
ALTER TABLE LatestEmailFileDates REBUILD
