Indexes can improve performance on your database however they do need to be maintained to. As more data is added to tables the corresponding data is added to an index somtimes in an inefficient order, this process is known as fragmentation.
To target fragmentation you first have to work out which indexes need optimising. I do this with the following code:
SELECT ID.object_id,
ID.index_id,
I.name AS Index_Name,
ID.Avg_Fragmentation_In_Percent AS Fragmentation,
T.name AS Table_Name
FROM sys.dm_db_index_physical_stats (db_id(), NULL,NULL, NULL, NULL) AS ID
JOIN sys.indexes AS I WITH(NOLOCK)
ON ID.Object_id = I.Object_id
AND ID.Index_id = I.Index_id
JOIN sys.objects AS T
ON T.object_id = I.object_id
WHERE Avg_Fragmentation_In_Percent >= 10
AND I.name IS NOT NULL
It ouputs all the indexes with fragmentation over 10% showing the index name, the table its associated with as well as the fragmentation in percent. Any fragmentation below 10% can be left alone.
For the remainder you can choose to rebuild or reorganize an index. A loose rule of thumb is to rebuild an index if its over 30% fragmented and reorganize if its over 10% but below 30%.
The syntax to reorganize or rebuild an index is relatively simple:
ALTER INDEX {INDEX_NAME} ON {TABLE_NAME}
REORGANIZE
Or
ALTER INDEX {INDEX_NAME} ON {TABLE_NAME}
REBUILD
Tags: Indexes