Time Slicing job gets stuck intermittently in a SQL Server based implementation

Products

• Clarity PPM On Premise

Issue/Introduction

• SQL Server is being used as the DB
• An issue has started occurring recently, wherein the 'Time Slicing' job gets stuck intermittently in the environment.

Cause

• A list of all deadlocked sessions is obtained from the DB during one of these occurrences
• By analyzing this it is understood that deadlocks are created by a session that obtained a lock on CMN_SEQ_CMN_AUDITS_TSV table
• This same session might have updated PRTEAM just before this.

Resolution

Identify TSVs that are audited in the system using the query being provided below. If auditing is not required on one or more of these TSVs then remove auditing on them.

To identify the root cause, find the action in the application that caused the deadlock.

Query:

SELECT
    aa.object_code AS object_code,
    obj_nls.name AS object_label,
    aa.attribute_code AS attribute_code,
    attr_nls.name AS attribute_label,
    attr_nls.custom_label AS attribute_custom_label,
    am.database_table AS database_table,
    am.database_column AS database_column,
    CASE
        aa.operation_code
        WHEN 'U' THEN 'Update'
        WHEN 'I' THEN 'Insert'
        WHEN 'D' THEN 'Delete'
        ELSE aa.operation_code
    END AS audited_operation,
    CASE
        aa.is_active
        WHEN 1 THEN 'Yes'
        ELSE 'No'
    END AS is_active,
    CASE
        am.is_custom
        WHEN 1 THEN 'Custom'
        ELSE 'System'
    END AS attribute_type
FROM
    odf_audited_attributes aa
    LEFT JOIN odf_attr_metadata am ON am.object_code = aa.object_code
    AND am.attribute_code = aa.attribute_code
    LEFT JOIN cmn_captions_nls obj_nls ON obj_nls.table_name = 'ODF_OBJECTS'
    AND obj_nls.pk_id = (
        SELECT
            id
        FROM
            odf_objects
        WHERE
            code = aa.object_code
    )
    AND obj_nls.language_code = 'en'
    LEFT JOIN cmn_captions_nls attr_nls ON attr_nls.table_name = 'ODF_ATTR_METADATA'
    AND attr_nls.pk_id = am.id
    AND attr_nls.language_code = 'en'
WHERE
    aa.audit_attribute_type = 2
ORDER BY
    aa.object_code,
    aa.attribute_code;