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:

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

  EXECUTE master.dbo.IndexOptimize
  @Databases = 'USER_DATABASES',
  @LogToTable = 'Y',
  @Indexes='EsAdministration.oper.AgentInformation'

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

  EXECUTE master.dbo.IndexOptimize
  @Databases = 'USER_DATABASES',
  @LogToTable = 'Y',
  @Indexes='ALL_INDEXES, -EsAdministration.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%.