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 ?
Varshini SAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
You can rebuild all the indexes with fragmentation > 30 and re frag the others
Use the maintenance plans mentioned here

http://ola.hallengren.com/ 

>Should I have to monitor the  avg_fragmentation every day ?
It depends on the no of inserts and updates on those tables, usually I perform this once a week
>Is there is any tool available in the market to monitor and performance this operation ?
use the query mentioned in the above link, that takes care of which to reindex
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.