Client Provided SolutionThe 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 ReviewCA 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.
NOTEThis 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.