Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 751
  • Last Modified:

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 ?
0
Varshini S
Asked:
Varshini S
1 Solution
 
Aneesh RetnakaranDatabase 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now