Query Causing High SQL CPU Usage

Products

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

Issue/Introduction

Intermittently, approximately every 30 days or so, the following CA Asset Portfolio Management/IT Asset Manager query consumes high CPU on the SQL Server where the MDB is located

SELECT *

FROM

(

SELECT resource_name;

resource_tag;

serial_number;

host_name;

mac_address;

dna_name;

ROW_NUMBER() OVER(ORDER BY audit_trail_date) ROW_NUMBER

FROM aud_ca_owned_resource

WHERE owned_resource_uuid = @p1

AND asset_source_uuid = @p2

)

SUBQUERY_ALIAS__

WHERE ROW_NUMBER__BETWEEN @START_ROW_NUMBER and @END_ROW_NUMBER

Environment

CA Asset Portfolio Management/IT Asset Manager 17.3 and 17.4
SQL Server MDB

Resolution

Add the the following index to the aud_ca_owned_resource MDB table

CREATE NONCLUSTERED INDEX [idx_ca_owned_resource] ON [dbo].[aud_ca_owned_resource]
(
       [own_resource_uuid] ASC,
       [asset_source_uuid] ASC
)
INCLUDE([resource_name],[host_name],[mac_address],[resource_tag],[serial_number],[dns_name],[audit_trail_date])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Note: SQL wait times will be minimized, but not eliminated