Index Tuning

One of the thing that might come handy for performance tuning is Database index tuning. To find out how the index pages are fragmented use the below query, which will result in showing all the indexes which have the fragmentation of above 30%.

SELECT OBJECT_NAME(I.OBJECT_ID) AS TABLENAME
, I.NAME AS TABLEINDEXNAME
, PHYSTAT.AVG_FRAGMENTATION_IN_PERCENT
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID('[[Database name]]'), NULL|OBJECT_ID('[[Table Name]]'), NULL, NULL, NULL) PHYSTAT
JOIN SYS.INDEXES I ON I.OBJECT_ID = PHYSTAT.OBJECT_ID AND I.INDEX_ID = PHYSTAT.INDEX_ID
WHERE PHYSTAT.AVG_FRAGMENTATION_IN_PERCENT > 30

Once we have the result, we can identify which index to tune up. i.e. either rebuild or reorganize. Below query can be used to rebuild the index

ALTER INDEX ALL ON [[Table name]] REBUILD