Delayed Server Response Message When accessing CI's or CO's with large numbers of CI relationships

Products

• CA Service Management - Service Desk Manager
• CA Service Desk Manager

Issue/Introduction

In some cases, end users may be experiencing a slowness in performance when accessing CI content and its associated relationships.  This can manifest when trying to view a given Configuration Item directly in CMDB or also when viewing a Change Order that has such a CI in place.

stdlogs may indicate large numbers of these entries:

01/01 10:37:46.96 SERVER1   sqlagt:select494    12420 SIGNIFICANT  sqlclass.c            1057 The following statement took 58392 milliseconds: Clause (;WITH cte$1 ( ci_rel_type, id, hier_child, hier_parent, "level", isCycle, cyclePath ) AS ( SELECT bmhier.ci_rel_type, bmhier.id, bmhier.hier_child, bmhier.hier_parent, 1 AS "level", 0 AS isCycle, '.' + CAST(bmhier.id AS VARCHAR(MAX)) AS cyclePath FROM busmgt bmhier INNER JOIN ca_owned_resource nr ON bmhier.hier_child = nr.own_resource_uuid WHERE bmhier.hier_parent IN (SELECT lrel_asset_chgnr.nr FROM usp_lrel_asset_chgnr lrel_asset_chgnr WHERE lrel_asset_chgnr.chg = 458882) AND ( bmhier.ci_rel_type IN (SELECT ci_rel_type.id FROM ci_rel_type WHERE ci_rel_type.is_peer = 1) ) UNION ALL SELECT bmhier.ci_rel_type, bmhier.id, bmhier.hier_child, bmhier.hier_parent, cte$1.level+1 AS "level", CASE WHEN cte$1.cyclePath LIKE '%.' + CAST(bmhier.id AS VARCHAR(MAX)) + '.%' THEN 1 ELSE 0 END AS isCycle, cte$1.cyclePath + '.' + CAST(bmhier.id AS VARCHAR(MAX)) AS cyclePath FROM busmgt bmhier INNER JOIN cte$1 ON cte$1.hier_child = bmhier.hier_parent AND cte$1.isCycle = 0 INNER JOIN ca_owned_resource nr ON bmhier.hier_child = nr.own_resource_uuid AND ( bmhier.ci_rel_type IN (SELECT ci_rel_type.id FROM ci_rel_type WHERE ci_rel_type.is_peer = 1) ) WHERE cte$1.level <= 3 ) SELECT nr.own_resource_uuid, cte$1.ci_rel_type, cte$1.id FROM cte$1 INNER JOIN ca_owned_resource nr ON nr.own_resource_uuid = cte$1.hier_child WHERE cte$1.isCycle = 0 AND (nr.resource_family IN (SELECT nrf.id FROM ca_resource_family nrf WHERE nrf.table_extension_name IN (?,?))) ORDER BY nr.resource_name) Input (<string>serx|<string>entservx)


01/01 18:11:10.56 SERVER1   sqlagt:select587    10320 SIGNIFICANT  sqlclass.c            1057 The following statement took 2329 milliseconds: Clause (;WITH cte$1 ( ci_rel_type, id, hier_child, hier_parent, "level", isCycle, cyclePath ) AS ( SELECT bmhier.ci_rel_type, bmhier.id, bmhier.hier_child, bmhier.hier_parent, 1 AS "level", 0 AS isCycle, '.' + CAST(bmhier.id AS VARCHAR(MAX)) AS cyclePath FROM busmgt bmhier INNER JOIN ca_owned_resource nr ON bmhier.hier_child = nr.own_resource_uuid WHERE bmhier.hier_parent IN (SELECT lrel_asset_chgnr.nr FROM usp_lrel_asset_chgnr lrel_asset_chgnr WHERE lrel_asset_chgnr.chg = 456826) AND ( bmhier.ci_rel_type IN (SELECT ci_rel_type.id FROM ci_rel_type WHERE ci_rel_type.is_peer = 1) ) UNION ALL SELECT bmhier.ci_rel_type, bmhier.id, bmhier.hier_child, bmhier.hier_parent, cte$1.level+1 AS "level", CASE WHEN cte$1.cyclePath LIKE '%.' + CAST(bmhier.id AS VARCHAR(MAX)) + '.%' THEN 1 ELSE 0 END AS isCycle, cte$1.cyclePath + '.' + CAST(bmhier.id AS VARCHAR(MAX)) AS cyclePath FROM busmgt bmhier INNER JOIN cte$1 ON cte$1.hier_child = bmhier.hier_parent AND cte$1.isCycle = 0 INNER JOIN ca_owned_resource nr ON bmhier.hier_child = nr.own_resource_uuid AND ( bmhier.ci_rel_type IN (SELECT ci_rel_type.id FROM ci_rel_type WHERE ci_rel_type.is_peer = 1) ) WHERE cte$1.level <= 3 ) SELECT nr.own_resource_uuid, cte$1.ci_rel_type, cte$1.id FROM cte$1 INNER JOIN ca_owned_resource nr ON nr.own_resource_uuid = cte$1.hier_child WHERE cte$1.isCycle = 0 AND (nr.resource_family IN (SELECT nrf.id FROM ca_resource_family nrf WHERE nrf.table_extension_name IN (?,?))) ORDER BY nr.resource_name) Input (<string>serx|<string>entservx)

Environment

Release : 17.1 or higher

Component : SERVICE DESK MANAGER

Cause

The above may be caused by a large number of inactive entries in the Business_Management.  The Business_Management table is known as the busmgt table within SQL and is in turn the bmhier object.

Resolution

One may consider performing the following:

Note:  Direct manipulation of the SDM Server's content is unsupported.  Please be sure to take all precautions and backups prior to attempting the following instructions:

- Access an Administrative command prompt on the SDM Server

- Perform a full extract of the Business_Management table:

pdm_extract Business_Management  > Business_Management-all.dat

- Run an extract of the inactive entries in Business_Management table:

pdm_extract -f "select * from Business_Management where del=1" > all_inactive_relations.txt

- Remove all of the inactive entries in Business_Management table and clear the table cache. 

pdm_load -r -f all_inactive_relations.txt
pdm_cache_refresh -t Business_Management

Additional Information

See also CA SDM Performance Problems - Quick Checklist (link)