Adding database indexes for performance improvement

This topic provides additional suggestions for improving performance based on internal benchmarks and field engagement.

To improve the performance of the Dashboard, Ticket Console, and Smart Recorder in Remedy with Smart IT, you can evaluate the following database (DB) indexes to your environments if BMC Remedy IT Service Management DB is Oracle or SQL. 

Disclaimer

Effectiveness of indexes depends on the distribution of data and usage of query criteria. To ensure better performance, carefully test the indexes before you implement them in your production environment.

Dashboard

DB - Oracle - Incident Stats

For Incident Stats - HPD:Help Desk

The query to get Incident stats is as follows:

Form: HPD:Help Desk

T-Table: T1851

SELECT COUNT(*)
FROM T1851 WHERE ((T1851.C1000000082 = 'COMPANY NAME') AND (T1851.C1000000563 >=1446909336) AND (T1851.C1000000563 <= 1449501336));

For Incident - HPD:Help Desk

The recommended index depends on the setting of the Centralized Configuration useSupportCompForStats parameter. 

For Incident - HPD:Help Desk
  • If the useSupportCompForStats parameter is set to false: From the BMC Remedy Developer Studio, create an overlay of the HPD:Help Desk form, and add the composite index on the fields Contact Company (1000000082) and Last Resolved Date (1000000563).
  • If the useSupportCompForStats parameter is set to true: From the BMC Remedy Developer Studio, create an overlay of the HPD:Help Desk form, and add the composite index on the fields Assigned Support Company (1000000251) and Last Resolved Date (1000000563).

DB - Oracle: Request Stats

For Request Stats - SRM:Request

The query to get Incident stats is as follows:

Form: SRM:Request

T-Table: T1398

SELECT C1000000162,C7,COUNT(*) FROM T1398 WHERE ((T1398.C1000003299 = 'COMPANY NAME')
AND ((T1398.C7 = 1200) OR (T1398.C7 = 2000) OR (T1398.C7 = 4000) OR (T1398.C7 = 1800) OR (T1398.C7 = 3000) OR (T1398.C7 = 5000) OR (T1398.C7 = 1500)))
GROUP BY C1000000162,C7 ORDER BY C1000000162,C7;

For Request - SRM:Request

For Request - SRM:Request

From the BMC Remedy Developer Studio, create an overlay of the SRM:Request form, and add the composite index on the fields Customer Company (1000003299), Assignee Group (10002506), Status (7), and Urgency (1000000162).

Ticket Console

DB - MS SQL Server: Open tickets Assigned to Me

For Change - CHG:Infrastructure Change

You may find performance issues explained in the following scenario.

The query to see All Open Tickets assigned to me is as follows:

Form: SMT:UnionSmartIT_TicketConsole

T-Table: T3257

SELECT  TOP 76 T3257.C1,C1000003884,C1000000218,C1000003863,C1000000232,C300267800,C1000000164,C301569808,C301569500,C1000000000,C1000005261,C301569907,C301569908,C301366100,C301626500,C1000000217 FROM T3257 WHERE ((T3257.C1000000841 = 'Bob') AND ((T3257.C301626500 = 0) OR (T3257.C301626500 = 5) OR (T3257.C301626500 = 8) OR (T3257.C301626500 = 11) OR (T3257.C301626500 = 1) OR (T3257.C301626500 = 2) OR (T3257.C301626500 = 3))) ORDER BY C1000000232 DESC, 1 ASC

Solution - From the BMC Remedy Developer Studio, create an overlay of the CHG:Infrastructure Change form, and add index on the field ASLOGID (1000003231).

After creating the index, to ensure that the database consumes it, update the database statistics for this form’s table using the following query:

T - Table ID for CHG:Infrastructure form is 1794.

USE [$DATABASE_NAME$]
GO
UPDATE STATISTICS [dbo].[T1794]
GO

After this, run the “my ticket” SQL query 2 (query with loginid where clause), and collect the SQL Execution plan. You will see performance improvements.

DB - ORACLE: Open tickets Assigned to Me

SMT:UnionSmartIT_TicketConsole

The query to see All Open Tickets Assigned to Me is as follows:

Form: SMT:UnionSmartIT_TicketConsole

 T-Table: T2894

SELECT * FROM (
SELECT T2894.C1,C1000000218,C1000003863,C1000000232,C300267800,C1000000164,C301569808,C301569500,C1000000000,C1000005261,C301569907,C301569908,C301366100,C301626500,C1000000217
FROM T2894 WHERE ((T2894.C1000000841 = 'smartit') AND ((T2894.C301626500 = 0) OR (T2894.C301626500 = 5) OR (T2894.C301626500 = 8) OR (T2894.C301626500 = 11) OR (T2894.C301626500 = 1))
AND ((T2894.C301569500 = 8000) OR (T2894.C301569500 = 18000) OR (T2894.C301569500 = 19000) OR (T2894.C301569500 = 70000) OR (T2894.C301569500 = 71000)
OR (T2894.C301569500 = 35000) OR (T2894.C301569500 = 18000) OR (T2894.C301569500 = 6000) OR (T2894.C301569500 = 34000) OR (T2894.C301569500 = 9000)
OR (T2894.C301569500 = 7000) OR (T2894.C301569500 = 10000) OR (T2894.C301569500 = 11000) OR (T2894.C301569500 = 12000) OR (T2894.C301569500 = 14000)
OR (T2894.C301569500 = 15000) OR (T2894.C301569500 = 72000)))
ORDER BY C1000000232 DESC, 1 ASC ) WHERE ROWNUM <= 76

For SRMRequest: SRM:Request

For SRM Request - SRM:Request

Assumption - the schema ID for "SRM:Request" is 1538.

Execute the following query to find the appropriate schema ID:

 SQL>select schemaId from arschema where name = 'SRM:Request'

Use the correct schema ID (T table Name), in this example:

create index T1538_C1000003231_C7Case on T1538
(
C1000003231,
CASE
WHEN C7 = 1000 THEN 1000
WHEN C7 = 2000 THEN 19000
WHEN C7 = 3000 THEN 70000
WHEN C7 = 4000 THEN 71000
WHEN C7 = 5000 THEN 18000
WHEN C7 = 6000 THEN 27000
WHEN C7 = 7000 THEN 28000
WHEN C7 = 8000 THEN 33000
WHEN C7 = 9000 THEN 32000
WHEN C7 = 1200 THEN 3000
WHEN C7 = 1500 THEN 4000
WHEN C7 = 1800 THEN 5000
END
);

DB - ORACLE: See 'All Open' tickets

SMT:UnionSmartIT_TicketConsole

The query to see All Open Tickets is as follows:

Form:SMT:UnionSmartIT_TicketConsole

T-Table: T2894

SELECT * FROM ( SELECT T2894.C1,C1000000218,C1000003863,C1000000232,C300267800,C1000000164,C301569808,C301569500,C1000000000,C1000005261,C301569907,C301569908,C301366100,C301626500,C1000000217
FROM T2894
WHERE (((T2894.C301626500 = 0) OR (T2894.C301626500 = 5) OR (T2894.C301626500 = 8) OR (T2894.C301626500 = 11) OR (T2894.C301626500 = 1) OR (T2894.C301626500 = 2) OR (T2894.C301626500 = 3))

AND ((T2894.C301569500 = 8000) OR (T2894.C301569500 = 18000) OR (T2894.C301569500 = 19000) OR (T2894.C301569500 = 70000) OR (T2894.C301569500 = 71000) OR (T2894.C301569500 = 35000)
OR (T2894.C301569500 = 18000) OR (T2894.C301569500 = 6000) OR (T2894.C301569500 = 34000) OR (T2894.C301569500 = 9000) OR (T2894.C301569500 = 7000) OR (T2894.C301569500 = 10000)
OR (T2894.C301569500 = 11000) OR (T2894.C301569500 = 12000) OR (T2894.C301569500 = 14000) OR (T2894.C301569500 = 15000) OR (T2894.C301569500 = 72000)
OR (T2894.C301569500 = 72001) OR (T2894.C301569500 = 3000)
OR (T2894.C301569500 = 17000) OR (T2894.C301569500 = 16000) OR (T2894.C301569500 = 13000)))
ORDER BY C1000000232 DESC, 1 ASC ) WHERE ROWNUM <= 76;
For Incident - HPD:Help Desk

Example
Assumption - the schema ID for "HPD:Help Desk" is 2078.

Execute the following query to find the appropriate schema ID:

SQL > select schemaId from arschema where name = 'HPD:Help Desk';

Use the correct schema ID (T table Name) in this example:

create index T2078_C7Case on T2078
(
CASE
WHEN C7 = 0 THEN 6000
WHEN C7 = 1 THEN 8000
WHEN C7 = 2 THEN 18000
WHEN C7 = 3 THEN 19000
WHEN C7 = 4 THEN 25000
WHEN C7 = 5 THEN 32000
WHEN C7 = 6 THEN 33000
END
);
For Change - CHG:Infrastructure Change

Example
Assumption - the schema ID for "CHG:Infrastructure Change" is 1924.

Execute the following query to find the appropriate schema ID:

SQL > select schemaId from arschema where name = 'CHG:Infrastructure Change';

Use the correct schema ID (T table Name) in this example:

create index T1924_C7Case on T1924
(
CASE
WHEN C7 = 0 THEN 72000
WHEN C7 = 1 THEN 7000
WHEN C7 = 2 THEN 9000
WHEN C7 = 3 THEN 10000
WHEN C7 = 4 THEN 11000
WHEN C7 = 5 THEN 12000
WHEN C7 = 6 THEN 14000
WHEN C7 = 7 THEN 15000
WHEN C7 = 8 THEN 19000
WHEN C7 = 9 THEN 28000
WHEN C7 = 10 THEN 27000
WHEN C7 = 11 THEN 32000
WHEN C7 = 12 THEN 33000
END
);
For SRMRequst - SRM:Request

Example

Assumption - the schema ID for "SRM:Request" is 1538.

Execute the following query to find the appropriate schema ID:

SQL>select schemaId from arschema where name = 'SRM:Request'

Use the correct schema ID (T table Name) in this example:

create index T1538_C7Case on T1538
(
CASE
WHEN C7 = 1000 THEN 1000
WHEN C7 = 2000 THEN 19000
WHEN C7 = 3000 THEN 70000
WHEN C7 = 4000 THEN 71000
WHEN C7 = 5000 THEN 18000
WHEN C7 = 6000 THEN 27000
WHEN C7 = 7000 THEN 28000
WHEN C7 = 8000 THEN 33000
WHEN C7 = 9000 THEN 32000
WHEN C7 = 1200 THEN 3000
WHEN C7 = 1500 THEN 4000
WHEN C7 = 1800 THEN 5000
END
);
For Task - TMS:Task

Example

Assumption - the schema ID for "TMS:Task" is 1280.

Execute the following query to find the appropriate schema ID:

SQL>select schemaId from arschema where name = 'TMS:Task'

Use the correct schema ID (T table Name) in this example:

create index T1280_C7Case on T1280
(
CASE
WHEN C7 = 1000 THEN 34000
WHEN C7 = 2000 THEN 8000
WHEN C7 = 3000 THEN 19000
WHEN C7 = 4000 THEN 35000
WHEN C7 = 5000 THEN 21000
WHEN C7 = 6000 THEN 32000
WHEN C7 = 7000 THEN 36000
END
);
For WorkOrder - WOI:WorkOrder

Example 

Assumption - the schema ID for "WOI:WorkOrder" is 2689.

Execute the following query to find the appropriate schema ID:

SQL>select schemaId from arschema where name = 'WOI:WorkOrder'

Use the correct schema ID (T table Name) in this example:

create index T2689_C7Case on T2689
(
CASE
WHEN C7 = 0 THEN 8000
WHEN C7 = 1 THEN 19000
WHEN C7 = 2 THEN 70000
WHEN C7 = 3 THEN 71000
WHEN C7 = 4 THEN 18000
WHEN C7 = 5 THEN 27000
WHEN C7 = 6 THEN 28000
WHEN C7 = 7 THEN 33000
WHEN C7 = 8 THEN 32000
END
);

DB - ORACLE: See 'Tickets Assigned to My Support Groups'

For Incident - HPD:Help Desk

Example

Assumption - the schema ID for "HPD:Help Desk" is 2078.

Execute the following query to find the appropriate schema ID:

SQL>select schemaId from arschema where name = 'HPD:Help Desk'

Use the correct schema ID (T table Name) in this example, to create Index on 'Assigned Group ID' column:

create index T2078_C7Case_C1000000079 on T2078
(
CASE
WHEN C7 = 0 THEN 6000
WHEN C7 = 1 THEN 8000
WHEN C7 = 2 THEN 18000
WHEN C7 = 3 THEN 19000
WHEN C7 = 4 THEN 25000
WHEN C7 = 5 THEN 32000
WHEN C7 = 6 THEN 33000
END,
   C1000000079
);   

        

For Request- SRM:Request

Example

Assumption - the schema ID for "SRM:Request" is 1538.

Execute the following query to find the appropriate schema ID:

SQL>select schemaId from arschema where name = 'SRM:Request'

Use the correct schema ID (T table Name) in this example, to create index on 'Assignee Group ID' column:

create index T1538_C7Case_C302760900 on T1538
(
CASE
WHEN C7 = 1000 THEN 1000              
WHEN C7 = 2000 THEN 19000
WHEN C7 = 3000 THEN 70000
WHEN C7 = 4000 THEN 71000
WHEN C7 = 5000 THEN 18000
WHEN C7 = 6000 THEN 27000
WHEN C7 = 7000 THEN 28000
WHEN C7 = 8000 THEN 33000
WHEN C7 = 9000 THEN 32000
WHEN C7 = 1200 THEN 3000
WHEN C7 = 1500 THEN 4000
WHEN C7 = 1800 THEN 5000
END,

C302760900
);
For Change - CHG:Infrastructure Change

Example

Assumption - the schema ID for "CHG:Infrastructure Change" is 1924.

Execute the following query to find the appropriate schema ID:

SQL>select schemaId from arschema where name = 'CHG:Infrastructure Change'

Use the correct schema ID (T table Name) in this example, to create index on 'ASGRPID' column:

create index T1924_C7Case_C1000003234 on T1924
(
CASE
       WHEN C7 = 0 THEN 72000
       WHEN C7 = 1 THEN 7000
       WHEN C7 = 2 THEN 9000
       WHEN C7 = 3 THEN 10000
       WHEN C7 = 4 THEN 11000
       WHEN C7 = 5 THEN 12000
       WHEN C7 = 6 THEN 14000
       WHEN C7 = 7 THEN 15000
       WHEN C7 = 8 THEN 19000
       WHEN C7 = 9 THEN 28000
       WHEN C7 = 10 THEN 27000
       WHEN C7 = 11 THEN 32000
       WHEN C7 = 12 THEN 33000
   END,
   C1000003234
);
For Task - TMS:Task

Example

Assumption - the schema ID for "TMS:Task" is 1280.

Execute the following query to find the appropriate schema ID:

SQL>select schemaId from arschema where name = 'TMS:Task'

Use the correct schema ID (T table Name) in this example, to create index on 'Assigned Group ID' column:

create index T1280_C7Case_C10002505 on T1280
(
   CASE
       WHEN C7 = 1000 THEN 34000
       WHEN C7 = 2000 THEN 8000
       WHEN C7 = 3000 THEN 19000
       WHEN C7 = 4000 THEN 35000
       WHEN C7 = 5000 THEN 21000
       WHEN C7 = 6000 THEN 32000
       WHEN C7 = 7000 THEN 36000
   END,
   C10002505
);
For WorkOrder - WOI:WorkOrder

Example

Assumption - the schema ID for "WOI:WorkOrder" is 2689.

Execute the following query to find the appropriate schema ID:

SQL>select schemaId from arschema where name = 'WOI:WorkOrder'

Use the correct schema ID (T table Name) in this example, to create index on 'ASGRPID' column :

create index T2689_C7Case_C1000003234 on T2689
(
   CASE
       WHEN C7 = 0 THEN 8000
       WHEN C7 = 1 THEN 19000
       WHEN C7 = 2 THEN 70000
       WHEN C7 = 3 THEN 71000
       WHEN C7 = 4 THEN 18000
       WHEN C7 = 5 THEN 27000
       WHEN C7 = 6 THEN 28000
       WHEN C7 = 7 THEN 33000
       WHEN C7 = 8 THEN 32000
   END,
   C1000003234
);

DB - ORACLE: See 'Tickets Assigned to My Support Groups' - STATS

SMT:UnionSmartIT_TicketConsole

Use the following query to see Stats on Ticket Console when Tickets assigned to My Support Group is selected:

Form :SMT:UnionSmartIT_TicketConsole

T-Table: T4781

SELECT C1000000164,COUNT(*) FROM T4781
WHERE (((T4781.C1000000217 = 'Frontline_Swedish') OR (T4781.C1000000217 = 'SUPHD - Supporting Helpdesk SE') OR (T4781.C1000000217 = 'BMC - iDesk Support SL1'))
AND ((T4781.C301626500 = 0) OR (T4781.C301626500 = 5) OR (T4781.C301626500 = 8) OR (T4781.C301626500 = 11) OR (T4781.C301626500 = 1))
AND ((T4781.C301569500 = 8000) OR (T4781.C301569500 = 18000) OR (T4781.C301569500 = 19000) OR (T4781.C301569500 = 70000) OR (T4781.C301569500 = 71000)
OR (T4781.C301569500 = 35000) OR (T4781.C301569500 = 18000) OR (T4781.C301569500 = 6000) OR (T4781.C301569500 = 34000) OR (T4781.C301569500 = 9000)
OR (T4781.C301569500 = 7000) OR (T4781.C301569500 = 10000) OR (T4781.C301569500 = 11000) OR (T4781.C301569500 = 12000) OR (T4781.C301569500 = 14000)
OR (T4781.C301569500 = 15000) OR (T4781.C301569500 = 72000)))
GROUP BY C1000000164 ORDER BY C1000000164

For Request - SRM:Request

For Request - SRM:Request

From the BMC Remedy Developer Studio, create an overlay of the SRM:Request form, and add the composite index on the fields Assignee Group (10002506), Status (7), and Priority (1000000164).

Smart Recorder

DB - ORACLE: Open and resolved tickets

For Incident - HPD:Help Desk

Example

Assumption - the schema ID for "HPD:Help Desk" is 2078.

Execute the following query to find the appropriate schema ID:

SQL>select schemaId from arschema where name = 'HPD:Help Desk'

Use the correct schema ID (T table Name) in this example:

create index T2078_C7CaseComposite on T2078
(
CASE
WHEN C7 = 0 THEN 6000
WHEN C7 = 1 THEN 8000
WHEN C7 = 2 THEN 18000
WHEN C7 = 3 THEN 19000
WHEN C7 = 4 THEN 25000
WHEN C7 = 5 THEN 32000
WHEN C7 = 6 THEN 33000
END,
C1000000080,
C1000000017
);
For Change - CHG:Infrastructure Change

Example

Assumption - the schema ID for "CHG:Infrastructure Change" is 1924.

Execute the following query to find the appropriate schema ID:

SQL>select schemaId from arschema where name = 'CHG:Infrastructure Change'

Use the correct schema ID (T table Name) in this example:

create index T1924_C7CaseComposite on T1924
(
CASE
WHEN C7 = 0 THEN 72000
WHEN C7 = 1 THEN 7000
WHEN C7 = 2 THEN 9000
WHEN C7 = 3 THEN 10000
WHEN C7 = 4 THEN 11000
WHEN C7 = 5 THEN 12000
WHEN C7 = 6 THEN 14000
WHEN C7 = 7 THEN 15000
WHEN C7 = 8 THEN 19000
WHEN C7 = 9 THEN 28000
WHEN C7 = 10 THEN 27000
WHEN C7 = 11 THEN 32000
WHEN C7 = 12 THEN 33000
END,
C1000003296
);
For SRM Request - SRM:Request

Example

Assumption - the schema ID for "SRM:Request" is 1538.

Execute the following query to find the appropriate schema ID:

SQL>select schemaId from arschema where name = 'SRM:Request'

Use the correct schema ID (T table Name) in this example:

create index T1538_C7CaseComposite on T1538
(
CASE
WHEN C7 = 1000 THEN 1000
WHEN C7 = 2000 THEN 19000
WHEN C7 = 3000 THEN 70000
WHEN C7 = 4000 THEN 71000
WHEN C7 = 5000 THEN 18000
WHEN C7 = 6000 THEN 27000
WHEN C7 = 7000 THEN 28000
WHEN C7 = 8000 THEN 33000
WHEN C7 = 9000 THEN 32000
WHEN C7 = 1200 THEN 3000
WHEN C7 = 1500 THEN 4000
WHEN C7 = 1800 THEN 5000
END,
C1000003296,
C1000000025
);
For Task - TMS:Task

Example

Assumption - the schema ID for "TMS:Task" is 1280.

Execute the following query to find the appropriate schema ID:

SQL>select schemaId from arschema where name = 'TMS:Task'

Use the correct schema ID (T table Name) in this example:

create index T1280_C7CaseComposite on T1280
(
CASE
WHEN C7 = 1000 THEN 34000
WHEN C7 = 2000 THEN 8000
WHEN C7 = 3000 THEN 19000
WHEN C7 = 4000 THEN 35000
WHEN C7 = 5000 THEN 21000
WHEN C7 = 6000 THEN 32000
WHEN C7 = 7000 THEN 36000
END,
C1000003296
);

DB - ORACLE: Person Profile

For CTM:People

When loading person's profile from Smart Recorder takes time, add the following index on the CTM:People schema table to solve performance problem.

 Assumption - the schema ID for 'CTM:People' is 1149.

create index T1149_FuncIndx_C4 on T1149(SYS_OP_C2C("C4"));
You may need to analyse this index with below mentioned SQL command -
ANALYZE TABLE T1149 COMPUTE STATISTICS;

DB - ORACLE: Stat Info "Escalations in last month"

For SRM:Request

The following queries get executed to fetch count of escalations in last month:  

The query to see Stat Info "Escalations in last month" is as follows:

Form: SRM:Request

T-Table: T1538

SELECT COUNT(*) FROM T1538       
WHERE (((T1538.C1000000338 = 'smartit') OR (T1538.C1000003296 = 'smartit'))
AND (T1538.C3 >= 1446901497)
AND ((T1538.C1000003009 = 2) OR (T1538.C1000003009 = 3)));

Solution: To get better performance for the above query, add the index via BMC Remedy Developer Studio in fields - Submit Date (3), Requested For Login ID (1000000338), and SLM Status (1000003009).

 

Form: HPD:Help Desk

T-Table: T2078

SELECT COUNT(*) FROM T2078
WHERE ((T2078.C3 >= 1446977662)
AND ((((T2078.C1000003009 = 3) OR (T2078.C1000003009 = 4))
AND ((T2078.C304309540 = 'smartit') OR (T2078.C1000000080 = 'smartit'))
AND (T2078.C301572100 IS NULL)) OR (((T2078.C1000003009 = 3) OR (T2078.C1000003009 = 4))
AND ((T2078.C304309540 = 'smartit') OR (T2078.C1000000080 = 'smartit')) AND (T2078.C301572100 IS NOT NULL) AND (T2078.C1000003009 = 0))));

Solution: To get better performance for the above query, add the index via BMC Remedy Developer Studio in the field Submit date (3).

 Asset Console

DB - MS SQL Server: Apply filters to asset console

For BMC.CORE:BMC_BaseElement

You may experience performance issues while applying filters to asset console when you have huge volume of asset data.

Solution - To get better performance for the asset CI search and asset console load apply the following index.

Assumption - the schema ID for 'BMC.CORE:BMC_BaseElement' is 471.

CREATE NONCLUSTERED INDEX asset_index
ON [dbo].[T471] ([C400127400],[C400079600])
INCLUDE ([C1],[C3],[C6],[C8],[C179],[C200000001],[C200000003],[C200000004],[C200000005],[C200000020],[C240001002],[C240001003],[C240001005],[C260100002],[C301172600],[C400129200],[C1000000001],[C200000012],[C260000005],[C260000001],[C200000007],[C260400003])

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*