Defragmenting indexes

Fragmented indexes and tables adversely affect the number of input/output operations. The fragmentation percentage of objects must therefore be kept as low as possible.

Use the IndexOptimize script to defragment the following tables:

  • EsLogs.Oper.AgentInformation, to be scheduled once a day. This table is processed separately to prevent deadlocks, as it is updated very frequently.

    EXECUTE [dbo].[IndexOptimize]
    @Databases = 'USER_DATABASES',
    @LogToTable = 'Y',
    @Indexes=EsLogs.oper.AgentInformation'

  • The other USER_DATABASES tables, to be scheduled once a day.

    EXECUTE [dbo].[IndexOptimize]
    @Databases = 'USER_DATABASES',
    @LogToTable = 'Y',
    @Indexes=ALL_INDEXES, -EsLogs.oper.AgentInformation'

Depending on the fragmentation percentage, these scripts use the following methods by default:

  • REBUILD, if the percentage exceeds 30%. In this method, objects are fully rebuilt. Data is rewritten on the disk contiguously and in order.
  • REORGANIZE, if the percentage lies between 5% and 30%. In this method, objects are partially rebuilt.
  • No defragmentation if the percentage falls below 5%.