The CA ITSM Database Administration Team (DBA) have identified the cause of a recent Delayed Server Response issue on site.

There is a missing index against the Call_Req table.
They have provided the following details of the index as well as two test queries.

Is this a known issue?
Can we add an index to our database to correc this issue?


NOTE
This symptom and resolution was provided for a specific client issue.
It may not be representative for all sites. Please exercise due diligence in applying findings.
This information is provided in as an aid to helping with similar issues.
 

Here are two sample SQL queries which illustrate the issue. Note that there are custom fields here as this was generated for a specific client site.

--query
 
Declare @P1 NVARCHAR(100)= 'My_ServiceDesk’, --cn01_ca_contact.last_name [table:ca_contact]
@P2 NVARCHAR(1000) = 'My_Human_Resources%', --prob_ctg.sym
@P3 NVARCHAR(12)= 'CLUPDC', --call_req.status
@P4 NVARCHAR(12)= 'RFI', --call_req.status
@P5 NVARCHAR(12)= 'RFCC', --call_req.status
@P6 int = 1548134420, --call_req.call_back_date
@P7 varbinary(16) = 0x25F70992D62B434B9C26569B5571058B, --grpmem.member
@P8 varbinary(16) = 0x25F70992D62B434B9C26569B5571058B, --call_req.customer
@P9 varbinary(16) = 0x25F70992D62B434B9C26569B5571058B; --call_req.assignee
 
SELECT call_req.open_date, call_req.id, cr_stat.sym, call_req.ref_num, call_req.id
FROM call_req, cr_stat, prob_ctg, ca_contact cn01_ca_contact
WHERE
    ( call_req.group_id = cn01_ca_contact.contact_uuid
        AND cn01_ca_contact.last_name = @P1
        AND call_req.category = prob_ctg.persid
        AND prob_ctg.sym LIKE @P2
        AND ( call_req.active_flag = 1 OR call_req.status = @P3 )
        AND call_req.status != @P4
        AND call_req.status != @P5
        AND ( ( ( call_req.call_back_date < @P6 OR call_req.call_back_date IS NULL )
        AND call_req.status = cr_stat.code AND cr_stat.hold = 1 )
        OR call_req.status = cr_stat.code AND cr_stat.hold = 0 ) )
    AND ( ( call_req.category = prob_ctg.persid AND prob_ctg.z_permission_group IS NULL )
        OR ( call_req.category = prob_ctg.persid AND prob_ctg.z_permission_group IS NOT NULL
        AND call_req.category = prob_ctg.persid
        AND prob_ctg.z_permission_group IN (SELECT grpmem.group_id FROM grpmem WHERE grpmem.member = @P7) )
        OR ( call_req.category = prob_ctg.persid AND prob_ctg.z_permission_group IS NOT NULL
        AND ( call_req.customer = @P8 OR call_req.assignee = @P9 ) ) )
ORDER BY call_req.open_date DESC





Second query:

-- Query 2
-- High I/O due to missing index

 
 
Declare @P8 varbinary(16) = 0x1B11A5BB53881C4A8EC7083139DBA6B4, --call_req.assignee
@P7 varbinary(16) = 0x1B11A5BB53881C4A8EC7083139DBA6B4, --call_req.customer
@P6 varbinary(16) = 0x1B11A5BB53881C4A8EC7083139DBA6B4, --grpmem.member
@P5 varbinary(16) = 0xFF8383EA6E29AF4F952205F228B0E124, --call_req.group_id
@P4 int = 1548118220, --call_req.call_back_date
@P3 NVARCHAR(12) = N'RFI', -- call_req.status
@P2 NVARCHAR(12) = N'CLUPDC', -- call_req.status
@P1 NVARCHAR(10) = N'R' --call_req.type
;
 
 
SELECT call_req.open_date, call_req.id, cr_stat.sym, call_req.ref_num, call_req.id
FROM call_req, prob_ctg, cr_stat
WHERE
    ( call_req.type = @P1
        AND ( call_req.active_flag = 1 OR call_req.status = @P2 )
        AND call_req.status != @P3
        AND ( ( ( call_req.call_back_date < @P4 OR call_req.call_back_date IS NULL )
            AND call_req.status = cr_stat.code AND cr_stat.hold = 1 )
            OR call_req.status = cr_stat.code AND cr_stat.hold = 0 )
        AND call_req.group_id = @P5 )
    AND ( ( call_req.category = prob_ctg.persid AND prob_ctg.z_permission_group IS NULL )
        OR ( call_req.category = prob_ctg.persid
        AND prob_ctg.z_permission_group IS NOT NULL
        AND call_req.category = prob_ctg.persid
        AND prob_ctg.z_permission_group IN (SELECT grpmem.group_id FROM grpmem WHERE grpmem.member = @P6) )
        OR ( call_req.category = prob_ctg.persid AND prob_ctg.z_permission_group IS NOT NULL
            AND ( call_req.customer = @P7 OR call_req.assignee = @P8 ) ) )
ORDER BY call_req.open_date DESC
 

CA Service Desk Manager (ITSM) 17.1

Client Provided Solution

The client DBA identified that the following query was needed to run CA Service Desk Manager (ITSM) 17.1 efficiently for their needs:
 

--Manually create the missing index
USE [mdb]

GO

SET ANSI_PADDING ON


GO

CREATE NONCLUSTERED INDEX [call_req_x201] ON [dbo].[call_req]
(
    [group_id] ASC,
    [type] ASC
)
INCLUDE (     [id],
    [ref_num],
    [active_flag],
    [open_date],
    [assignee],
    [status],
    [customer],
    [category],
    [call_back_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)

GO

CA Engineering Review

CA Service Desk Manager (ITSM) should have its database managed like any other application.
It is up to sites to maintain or modify the indices against the database. Indexes may be added or modified as needed to suit. Sometimes it is better to modify an index than add a new one.

This specific change is not going ahead as an index to be brought into the product, as the underlying queries may not be used by other sites, and could waste resources.

There is already present a similar index, _x5, which has as the columns, group_id, active_flag and type.
If index this is already present, but there are still improvements with the new index, then please continue with the plan to add it.

NOTE
This symptom and resolution was provided for a specific client issue.
It may not be representative for all sites. Please exercise due diligence in applying findings.
This information is provided in as an aid to helping with similar issues.
Please back up your database before making changes, as inadvertent data loss or degraded performance could be caused by direct database changes.

How to extend or disable the "Delayed Server Response" timeout in CA SDM?

Troubleshooting CA Service Desk Manager

How to address "Delayed Server Response Message" in Service Desk web interface.

Users receive 'Delayed Server Response' message when attempting to open certain CA Service Desk Manager (CA SDM) Knowledge Documents

Trouble-shooting "Delayed server response" on Change Orders with many Configuration Items