Is it essential to rebuild table indexes for a CA Workload Automation AE Database?

Document ID : KB000067205
Last Modified Date : 05/03/2018
Show Technical Document Details
Question:

Is it essential to rebuild table indexes for a CA Workload Automation AE Database?

We do not see any invalid or unusable indexes in the CA Workload Automation AE database. Is it still necessary rebuild indexes, periodically?

 

Environment:
CA Workload Automation AE 11.3+
Answer:

Yes, it is essential to rebuild table indexes for CA Workload Automation AE Database.

Explanation:

Indexes are optional structures associated with tables that allow SQL statements to execute quickly. As we keep running more and more jobs or inserting/updating/deleting the Workload resources like jobs, events, machines, calendars etc., tables grow and current indexes grow in size and so they become inefficient. Growth (or increased size of indexes) can produce index fragmentation. An index requires maintenance when the index fragmentation level adversely affects performance. The level of index fragmentation could vary based on the size of the index and number of data pages.

Infrequent Database maintenance and Table index rebuild could increase the fragmentation levels of database indexes and will have ramifications, including:

  • Slow the performance of the Scheduler or Application Server.
  • Query will timeout and it can impact job processing.
  • Report (autorep, forecast, etc) retrieval takes time.
  • Workload Control Center performance will be impacted when the as_server is slow in getting the data.
  • DB Monitor takes significant amount of time in evaluating the connections and can lead to slow Scheduler or Application Server.
Additional Information:
https://docops.ca.com/ca-workload-automation-ae/11-4-2/en/administrating/ae-configuration/maintain-the-event-server/rebuild-table-indexes-for-a-ca-workload-automation-ae-database