Delayed Server Response due to missing Call_Req database index, for ITSM
Article ID: 126714
Updated On:
Products
SUPPORT AUTOMATION- SERVER
CA Service Desk Manager - Unified Self Service
KNOWLEDGE TOOLS
CA Service Management - Asset Portfolio Management
CA Service Management - Service Desk Manager
Issue/Introduction
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.
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.
Cause
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
--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
Environment
CA Service Desk Manager (ITSM) 17.1
Resolution
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:
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.
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.
Additional Information
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
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
Feedback
Yes
No
Powered by
