Knowing the need of rebuilding/reorganizing indexes and updation of statistics in EngageOne Delivery Audit Databases

When data is inserted, updated, or deleted in a database table and if indexes exist to operate on the table, they must be maintained to reflect the table data changes.

Fragmentation occurs when the logical ordering of the index does not match with its physical ordering. This causes SQL Server to make extra efforts to return ordered results.The column avg_fragmentation_in_percent in the SQL Server default  function  sys.dm_db_index_physical_stats. This function accepts parameters, such as the database, database table, and index for which you want to find fragmentation. These options allow the user to specify the level of detail that you may want to see with respect to index fragmentation. The sys.dm_db_index_physical_stats function returns data related to a specific table or index in a tabular format.

Based on the value in the avg_fragmentation_in_percent column, you can decide whether to reorganize or rebuild an index. Generally, you are recommended to perform the following operations:
  • If value in the avg_fragmentation_in_percent column is less than 10% - No Action Required
  • If value in the avg_fragmentation_in_percent column is greater than 10% and less than or equal to 30% - Reorganize an index and Update Statistics.
  • If value in the avg_fragmentation_in_percent column is greater than 30% - Rebuild the indexes.
For details follow sys.dm_db_index_physical_stats.
UPDATED:  September 26, 2017