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%.