Performance degradation on RH whilst saving reports

Products

• CA Automic Workload Automation - Automation Engine
• Automic SaaS
• CA Automic One Automation

Issue/Introduction

After upgrading the Automation Engine to v21.0.13 or 24.0.0, the performance of the AE is degraded when using a SQL Server database. 

Possibly, multiple WP processes are at 100 in the B.01 column in AWI

The WP logs contain many time-critical DB calls, taking sometimes up to 30 seconds, and with exactly the following query on RH:

20250416/151411.612 - U00003524 UCUDB: ===> Time critical DB call!       OPC: 'EXEC' time: '8:896.318.499'
20250416/151411.612 - U00003525 UCUDB: ===> 'update RH set RH_TimeStamp4 = ? from RH with (forceseek, readpast) where RH_AH_Idnr = ? and RH_TimeStamp4 is null and RH_Type not in (select AH_Archive2 from AH where AH_OType = 'REPORT' and AH_ParentAct = ?)'

Environment

Component: Automation Engine

Database: MS SQL

Cause

DE173107

Resolution

Workaround:

You should change the following 2 indices within the database.  Please note this can be done with the system still online due to the WITH (ONLINE = ON) clause.  That said, always confirm with a DBA before taking action like this.

On SQL Server:

drop index NK_AH_ParentAct on AH;
CREATE NONCLUSTERED INDEX NK_AH_ParentAct ON AH
(
    [AH_ParentAct] ASC,
    [AH_OType] ASC,
    [AH_Archive2] ASC,
    [AH_Idnr] ASC
) WITH (ONLINE = ON);

and

drop index NK_RH_Timestamp4 on RH;
CREATE NONCLUSTERED INDEX NK_RH_Timestamp4 ON RH
(
    [RH_AH_Idnr] ASC,
    [RH_Type] ASC,
    [RH_TimeStamp4] ASC
) WITH (ONLINE = ON);

Solution:

Update to a fix version listed below or a newer version if available.

Fix version:
Component(s): Automation Engine

Automation.Engine 21.0.15 - Available 
Automation.Engine 24.4.2   - Available 

Additional Information

Public Description: A problem has been fixed where index extension made report saving faster.