ITAM Performance after upgrade

Products

• CA IT Asset Manager Asset Portfolio Management
• CA Service Desk Manager
• CA Service Management - Service Desk Manager
• CA Service Management - Asset Portfolio Management

Issue/Introduction

Reporting slowness when trying to create/update an asset in both SDM and ITAM.

From the ITAM Data Importer log one can see the following:

ERROR CA.Applications.DataImporterEngine.ProcessorCommon - Web Service threw exception. Retry '1' of maximum allowed retries '3'. Exception message 'System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Transaction (Process ID 263) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In SDM, the stdlogs have entries that reference the following queries taking a long time to run in table ca_owned_resource:

SDM-SERVER sqlagt:upinde       15668 SIGNIFICANT  sqlclass.c            1057 The following statement took 9141 milliseconds: Clause (UPDATE ca_owned_resource SET last_update_date = ? , last_update_user = ? , resource_contact_uuid = ? , version_number = ? WHERE own_resource_uuid = ?) Input (<last_update_date:time>04/21/2021 17:20:25|<last_update_user:string><User>|<resource_contact_uuid:uuid>XXXXXXXX|<version_number:int>18|<own_resource_uuid:uuid>XXXX)

Environment

CA Asset Portfolio Management 17.3 and 17.4

Resolution

The READ_COMMITTED_SNAPSHOT setting can be safely enabled on the MDB database by running the below query.

ALTER DATABASE mdb SET READ_COMMITTED_SNAPSHOT ON;

As always, it is advised to test the above functionality in a non-prod instance and check if the data saved after a CI update is desired and consistent.  This is because enabling READ_COMMITTED_SNAPSHOT can cause certain data inconsistencies, if a SQL Read happens on the data row that is being modified immediately (also known as a dirty read).

However, if there is no such sequence in a transaction, it does not cause any issues and it is okay to enable the setting.