CPU usage is 100% and slow performance of mdb and Service Desk Manager

Products

• CA Service Management - Service Desk Manager

Issue/Introduction

Several sessions are in "AWAITING COMMAND" and the CPU usage of the Database server is close to 100%

This started to occur after creating a stored query, which is similar to:

SELECT DISTINCT chg.open_date, chg.sched_start_date, chg.sched_end_date, chg.id, chg.risk, chg.chg_ref_num, chg.id FROM chg, prp WHERE prp.object_id = chg.id AND prp.value = @P1 AND prp.object_id = chg.id AND prp.label = @P2 AND chg.active_flag = 1 ORDER BY chg.open_date DES

Environment

CA SERVICE DESK MANAGER 17.3 and higher

Resolution

Database DBA can create index to help the performance. For example, create the following index:


USE [mdb] GO CREATE NONCLUSTERED INDEX [] ON [dbo].[prp] ([value],[label]) INCLUDE ([object_id]) GO