Modify DB properties to optimize reporting queries

Products

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

Issue/Introduction

A suggestion was raised to adjust the values of the [mdb], specifically the properties snapshot_isolation_state_desc and is_read_committed_snapshot_on to the values:

snapshot_isolation_state_desc: ON

is_read_committed_snapshot_on: 1

What would be the effects of such a change to the Service Management product?

OOTB settings from Support lab:

 

Environment

Release : 17.3 or higher

Component :  CA Service Management MDB

Resolution

The recommendation is not to make changes to MDB. With all the extra activities which will cause additional load, DB performance will suffer and SDM apps' performance will suffer. Locking is already done in virtual DB for SDM so changing does not help at least for the SDM side of the equation.

To better improve performance, consider using database replication and reporting from the replication DB (link)