search cancel

Expensive query in Asset Portfolio Management

book

Article ID: 257766

calendar_today

Updated On:

Products

CA IT Asset Manager Asset Portfolio Management

Issue/Introduction

Our DBAs have identified a query that is taking up resources and also one runs very often.  Can you tell me if this is an OOTB query or is there a way for us to identify who created it and where it is used.  The query looks to be a CO query:

(@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

Release : 17.3

Resolution

This query is for asset auditing and it runs very often when you have asset access and modification. It can't stop as it is designed to run in the background.

To make the query run more efficient one should consider purge the 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+ so if you run older rollup patches please consider upgrading to current rollup patch to have this feature. It is available as a menu once an APM admin logs in he/she will see the menu at the top row.