Expensive query in Asset Portfolio Management

Products

• CA IT Asset Manager Asset Portfolio Management

Issue/Introduction

DBA has identified a query that is taking up resources and also one that runs very often. 

The query looks to be a CO query.

Is this an OOTB query or is there a way to identify who created it and where it is used?

(@START_ROW_NUMBER int,@END_ROW_NUMBER int)SELECT * FROM (SELECT c0.last_update_date c0_a54,c0.tenant c0_a116,c0.audit_trail_type c0_a14,c0.inactive c0_a47,c0.lifecycle_status c0_a60,c0.version_number c0_a123,c0.own_resource_uuid c0_a4,c0.audit_trail_id c0_a12,ROW_NUMBER() OVER(ORDER BY c0.own_resource_uuid ASC,c0.audit_trail_id ASC) ROW_NUMBER__ FROM dbo.al_aud_owned_resource_view c0 WITH(READPAST)) SUBQUERY_ALIAS__ WHERE ROW_NUMBER__ BETWEEN @START_ROW_NUMBER AND @END_ROW_NUMBER

 

 

Environment

CA IT Asset Portfolio Management 17.3 and 17.4

Resolution

This query is for asset auditing and it runs very often when you have asset access and modification. It cannot be stopped as it is designed to run in the background.

To make the query run more efficient one should consider purging the CA Asset Portfolio Management audit data. Audit data would be huge after certain period of operation so it is important to purge the historic audit data.

The audit purge feature is available from 17.3 RU11 onwards.

The feature is available as a menu in the top row once an APM admin logs in.