Link to home
Start Free TrialLog in
Avatar of Varshini S
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_fragmentation_in_percent > 30



SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
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
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 ?
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial