CA WA DE (dSeries) : MS-SQL Deadlocks and performance issues.
Article ID: 185688
CA Workload Automation DE - Business Agents (dSeries)CA Workload Automation DEDSERIES- SERVERCA Workload Automation DE - System Agent (dSeries)CA Workload Automation DE - Scheduler (dSeries)
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.
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.
Release : 12.x
Component : CA WORKLOAD AUTOMATION DE (DSERIES) Database: MS SQL
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.