Varshini S
asked on
SQL database index fragmentation percentage
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_fragmentati on_in_perc ent > 30
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentati on_in_perc ent
FROM sys.dm_db_index_physical_s tats(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_fragmentati on_in_perc ent > 30
ORDER BY indexstats.avg_fragmentati on_in_perc ent DESC
I have given sample output
Output:
Table name index Name Index Type avg_fragmentation_in_perce nt
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 ?
SELECT OBJECT_NAME(ind.OBJECT_ID)
ind.name AS IndexName, indexstats.index_type_desc
indexstats.avg_fragmentati
FROM sys.dm_db_index_physical_s
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentati
ORDER BY indexstats.avg_fragmentati
I have given sample output
Output:
Table name index Name Index Type avg_fragmentation_in_perce
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.