CA WA DE (dSeries) : MS-SQL Deadlocks and performance issues.

Products

• CA Workload Automation DE - Business Agents (dSeries)
• CA Workload Automation DE
• DSERIES- SERVER
• CA Workload Automation DE - System Agent (dSeries)
• CA Workload Automation DE - Scheduler (dSeries)

Issue/Introduction

This Knowledge Document refers to DE (dSeries) setup with MS SQL database.
DE sometimes takes long time to start jobs.  Jobs get delayed and cause scheduling issues.

Environment

Release : 12.x

Component : CA WORKLOAD AUTOMATION DE (DSERIES)
Database: MS SQL Server

Cause

Tracelogs show extensive SQL deadlocks.

202002XX 10:00:00.000 [relationaldatabase] [ERROR] DM.Appl.HKG_DWH.115: [2020-02-XX_10:00:00.000] SQL Exception for query: DELETE FROM ESP_PUBLISHERS WHERE ID = ?; the exception is: Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Resolution

The deadlocks are regularly observed in MS SQL. They are cleared by SQL server side and DE has no control over them.  Unfortunately, this results in performance issues and also in delay of jobs running on time.  When deadlocks occur, the DE Server has to wait for the deadlock to clear. 

For more details, please consult your DBA.  Run SQL profiler and trace the lock flags 1222 and 1204.   DBA may also setup clustered index on Primary key and non-clustered index on other columns.

Suggested workaround/recommendation in DE (dSeries).