SQL database index fragmentation percentage
Posted on 2014-02-12
I am not familiar with database index fragmentation. I spoke to my friend regarding my database poor performance, he advised me to do the re-index. Based on his input I have executed the following SQL query and it is shows all the table having indexstats.avg_fragmentation_in_percent > 30
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC
I have given sample output
Table name index Name Index Type avg_fragmentation_in_percent
Employee NULL Heap 98.75
Sales PK_SALES CLUSTERED INDEX 85
Customer NULL Heap 45
Vendor PK_VEN CLUSTERED INDEX 60
Should I have to re-index all the above tables ?
Employee & Customer does not have index . Should I have to create a index for that table ?
Does Re-build required or not ?
Should I have to monitor the avg_fragmentation every day ?
Is there is any tool available in the market to monitor and performance this operation ?