Tuning the Microsoft SQL Server indexes

Overview

Indexes provide significant performance improvements when measured against a broad performance test workload. However, database platform performance gains might vary depending on factors such as application usage, load patterns, hardware sizing, application, and database server configuration. Database administrators must monitor databases for efficient index usage to identify the impact of applying the recommended indexes. They must also determine other indexes that improve performance based on situational and data composition needs.

The indexes that are listed are not included in the TRIRIGA® base product unless otherwise stated.

Application platform indexes

Add the following application platform tuning indexes to Microsoft SQL Server databases:

CREATE INDEX [PERF_APP_OBJECT_PERMISSION1] ON [APP_OBJECT_PERMISSION] ([APPLICATION_ID], [TEMPLATE_ID], [TAB_ID], [SECTION_ID], [FIELD_ID], [SERVICE_ID], [GROUP_ID])
GO

CREATE INDEX [PERF_APP_OBJECT_PERMISSION2] ON [APP_OBJECT_PERMISSION] ([TAB_ID], [SECTION_ID], [FIELD_ID], [TEMPLATE_ID], [SERVICE_ID], [GROUP_ID])
GO

CREATE INDEX [PERF_BUDGET_CODES1] ON [BUDGET_CODES] ([STATUS], [CODE_REF_ID], [TRANSACTION_ID])
GO

CREATE INDEX [PERF_BUDGET_CURRENCIES1] ON [BUDGET_CURRENCIES] ([TRANSACTION_ID], [CURRENCY_CODE], [AMOUNT])
GO

CREATE INDEX [PERF_BUDGET_TRANSACTION1] ON [BUDGET_TRANSACTION] ([TRANSACTION_TYPE], [REVERSE_FLAG], [SYSTEM_DATE])
GO

CREATE INDEX [PERF_BUDGET_TRANSACTION2] ON [BUDGET_TRANSACTION] ([OBJECT_ID], [REVERSE_FLAG]) INCLUDE ([TRANSACTION_ID], [TRANSACTION_TYPE], [DESCRIPTION], [TRANSACTION_DATE], [SYSTEM_DATE], [COMPANY_ID], [PROGRAM_ID], [PROJECT_ID], [BO_ID], [OBJECT_VERSION], [MODULE_ID], [ORGANIZATION_ID], [GEOGRAPHY_ID], [USER_ID], [REF_OBJECT_ID], [REF_OBJECT_VERSION], [REF_MODULE_ID], [REF_BO_ID], [REVERSE_DATE], [LOCATION_ID])
GO

CREATE INDEX [PERF_GROUPMEMBER1] ON [T_GROUPMEMBER] ([PAR_SPEC_ID], [MEMBERTYPE], [SYS_OBJECTID]) INCLUDE ([MEMBERID])
GO

CREATE INDEX [PERF_GUI_HEADER_PUBL1] ON [GUI_HEADER_PUBL] ([GUI_NAME])
GO

CREATE INDEX [PERF_GUI_HEADER_PUBL2] ON [GUI_HEADER_PUBL] ([SPEC_CLASS_TYPE], [ALT_PRINT_FORM])
GO

CREATE INDEX [PERF_IBS_SPEC1] ON [IBS_SPEC] ([ROOT_FLG]) INCLUDE ([SPEC_CLASS_TYPE], [SPEC_ID])
GO

CREATE INDEX [PERF_IBS_SPEC_TYPE1] ON [IBS_SPEC_TYPE] ([COMPANY_ID], [SPEC_CLASS_TYPE], [DELETED_FLAG])
GO

CREATE INDEX [PERF_IBS_SPEC_TYPE2] ON [IBS_SPEC_TYPE] ([DELETED_FLAG], [EXT_MANAGED], [PASS_THROUGH_FLAG], [SHOW_IN_MANAGER], [SPEC_CLASS_TYPE])
GO

CREATE INDEX [PERF_IBS_SPEC_TYPE3] ON [IBS_SPEC_TYPE] ([NAME], [DELETED_FLAG])
GO

CREATE INDEX [PERF_IBS_TEMP_SPEC_ASSIGNMENTS1] ON [IBS_TEMP_SPEC_ASSIGNMENTS] ([RAND_NO], [SPEC_ID])
GO

CREATE INDEX [PERF_IBS_TEMP_SPEC_ASSIGNMENTS2] ON [IBS_TEMP_SPEC_ASSIGNMENTS] ([RAND_NO], [SPEC_ID], [ASS_TYPE]) INCLUDE ([ASS_SPEC_ID], [ACTION])
GO

CREATE INDEX [PERF_LIST_VALUE1] ON [LIST_VALUE] ([LIST_ID], [COMPANY_ID], [LANGUAGE_ID])
GO

CREATE INDEX [PERF_ORGANIZATION1] ON [T_ORGANIZATION] ([SYS_OBJECTID], [SYS_GUIID]) INCLUDE ([TRISTATUSCL])
GO

CREATE INDEX [PERF_ORGANIZATION2] ON [T_ORGANIZATION] ([SYS_OBJECTID], [SYS_GUIID]) INCLUDE ([SPEC_ID], [SYS_TYPE1], [TRIIDTX], [TRINAMETX], [TRISTATUSCL], [TRIPATHTX], [TRISHORTNAMETX], [TRIORGTYPECL], [TRIORGTYPECLOBJID])
GO

CREATE INDEX [PERF_ORGANIZATION3] ON [T_ORGANIZATION] ([SYS_PROJECTID], [SYS_OBJECTID]) INCLUDE ([SPEC_ID], [SYS_GUIID], [SYS_TYPE1], [SYS_ORGNAME], [SYS_ORGNAMEOBJID], [TRIIDTX], [TRINAMETX], [TRISTATUSCL], [TRIFORMLABELSY])
GO

CREATE INDEX [PERF_RESOURCE_AVAILABILITY1] ON [RESOURCE_AVAILABILITY] ([SPEC_ID], [TASK_ID])
GO

CREATE INDEX [PERF_SCHEDULEDEVENTS1] ON [T_SCHEDULEDEVENTS] ([EVENTSTATUS], [SYS_OBJECTID], [ENDDATETIME]) INCLUDE ([SPEC_ID], [SYS_GUIID], [SYS_TYPE1], [STARTDATETIME])
GO

CREATE INDEX [PERF_TRIBUILDING0] ON [T_TRIBUILDING] ([SYS_GUIID], [SYS_OBJECTID])
GO

CREATE INDEX [PERF_TRIBUILDING1] ON [T_TRIBUILDING] ([SYS_PROJECTID], [SYS_OBJECTID]) INCLUDE ([TRIGROSSMAREAMETNU], [TRIGROSSMAREAMETNU_UOM], [TRIGROSSMAREAIMPNU], [TRIGROSSMAREAIMPNU_UOM], [TRIAREAUO], [TRIBUILDINGCOMMONAREAN], [SPEC_ID], [SYS_GUIID], [SYS_TYPE1], [TRINAMETX], [TRIUSERMESSAGEFLAGTX], [TRIFORMLABELSY], [TRIPATHTX], [TRIPARENTPROPERTYTX], [TRIPARENTPROPERTYTXOBJID], [TRIBUILDINGCLASSCL], [TRIBUILDINGCLASSCLOBJID], [TRINUMBEROFFLOORSNU], [TRIGROSSAREAMETNU], [TRIGROSSAREAMETNU_UOM], [TRIGROSSAREAIMPNU], [TRIGROSSAREAIMPNU_UOM], [TRILENGTHUO])
GO

CREATE INDEX [PERF_TRIBUILDING2] ON [T_TRIBUILDING] ([SYS_PROJECTID], [SYS_OBJECTID], [SYS_GUIID]) INCLUDE ([TRINAMETX], [TRISTATUSCL])
GO

CREATE INDEX [PERF_TRIBUILDINGFACT1] ON [T_TRIBUILDINGFACT] ([TRICAPTUREPERIODTXOBJID]) INCLUDE ([TRIFACTCAPITALFIXEDASS], [TRIDIMBUILDINGTENURETXOBJID], [TRIDIMBUILDINGCLASSTXOBJID], [TRIDIMLOCATIONTXOBJID], [TRIFACTREPLACEMENTVALU])
GO

CREATE INDEX [PERF_TRIBUILDINGFACT2] ON [T_TRIBUILDINGFACT] ([TRICAPTUREPERIODTXOBJID]) INCLUDE ([TRIFACTMAINTENANCECOST], [TRIDIMBUILDINGTENURETXOBJID], [TRIDIMBUILDINGCLASSTXOBJID], [TRIDIMLOCATIONTXOBJID], [TRIFACTREPLACEMENTVALU])
GO

CREATE INDEX [PERF_TRIBUILDINGSYSTEMITEMFACT1] ON [T_TRIBUILDINGSYSTEMITEMFACT] ([TRICAPTUREPERIODTXOBJID]) INCLUDE ([TRIDIMBUILDINGTENURETXOBJID], [TRIDIMBUILDINGCLASSTXOBJID], [TRIFACTREPLACEMENTVALU], [TRIDIMLOCATIONTXOBJID], [TRIFACTESTIMATEDREPAIR], [TRIDIMBUILDINGSYSTEMCLOBJID])
GO

CREATE INDEX [PERF_TRICAPITALPROJECT1] ON [T_TRICAPITALPROJECT] ([SYS_PROJECTID], [SYS_GUIID], [SYS_OBJECTID]) INCLUDE ([TRIDATEDA], [SPEC_ID], [SYS_TYPE1], [TRIIDTX], [TRINAMETX], [TRISTATUSCL], [TRISTATUSCLOBJID])
GO

CREATE INDEX [PERF_TRICAPITALPROJECT2] ON [T_TRICAPITALPROJECT] ([SYS_PROJECTID], [SYS_GUIID], [SYS_OBJECTID]) INCLUDE ([TRINAMETX], [TRISTATUSCL])
GO

CREATE INDEX [PERF_TRICAPITALPROJECTFACT1] ON [T_TRICAPITALPROJECTFACT] ([TRIDIMUSERIDTXOBJID], [TRIDIMSTATUSTX], [TRICAPTUREPERIODTXOBJID], [TRIDIMPROGRAMTXOBJID]) INCLUDE ([TRIFACTBUDGETCURRENTAM], [TRIFACTCOMMITMENTCHANG], [TRIDIMPROGRAMTX])
GO

CREATE INDEX [PERF_TRICAPITALPROJECTFACT2] ON [T_TRICAPITALPROJECTFACT] ([TRIDIMUSERIDTXOBJID], [TRIDIMSTATUSTX], [TRICAPTUREPERIODTXOBJID], [TRIDIMPROGRAMTXOBJID]) INCLUDE ([TRIFACTCURRENTBUDGETTO], [TRIFACTBUDGETCURRENTAM], [TRIDIMPROGRAMTX])
GO

CREATE INDEX [PERF_TRICAPITALPROJECTFACT3] ON [T_TRICAPITALPROJECTFACT] ([TRIDIMUSERIDTXOBJID], [TRIDIMSTATUSTX], [TRICAPTUREPERIODTXOBJID], [TRIDIMPROGRAMTXOBJID]) INCLUDE ([TRIFACTORIGINALBUDGETT], [TRIFACTBUDGETORIGINALA], [TRIDIMPROGRAMTX])
GO

CREATE INDEX [PERF_TRICAPITALPROJECTFACT4] ON [T_TRICAPITALPROJECTFACT] ([TRIDIMUSERIDTXOBJID], [TRIDIMSTATUSTX], [TRICAPTUREPERIODTXOBJID], [TRIDIMPROGRAMTXOBJID]) INCLUDE ([TRIFACTSCHEDULEVARIANC], [TRIFACTCOUNTTOTALNUMBE2], [TRIDIMPROGRAMTX])
GO

CREATE INDEX [PERF_TRICLAUSETYPE1] ON [T_TRICLAUSETYPE] ([TRINAMETX])
GO

CREATE INDEX [PERF_TRIPEOPLE1] ON [T_TRIPEOPLE] ([TRIIDTX])
GO

CREATE INDEX [PERF_TRIPEOPLE2] ON [T_TRIPEOPLE] ([TRIRECORDNAMESY])
GO

CREATE INDEX [PERF_TRIPEOPLE3] ON [T_TRIPEOPLE] ([SYS_GUIID], [SYS_OBJECTID]) INCLUDE ([TRIWORKFAXTX], [TRIWORKPHONETX], [TRIEMAILTX], [TRISTATUSCL], [TRITITLETX], [PRIMARYORGANIZATIONSYSKEY], [SPEC_ID], [SYS_TYPE1], [TRIUSERMESSAGEFLAGTX], [TRINAMETX])
GO

CREATE INDEX [PERF_TRIPEOPLE4] ON [T_TRIPEOPLE] ([SYS_GUIID], [SYS_OBJECTID], [TRIIDTX])
GO

CREATE INDEX [PERF_TRIPEOPLE5] ON [T_TRIPEOPLE] ([SYS_OBJECTID], [SYS_GUIID]) INCLUDE ([TRIFIRSTNAMETX], [TRILASTNAMETX], [TRISTATUSCL], [PRIMARYORGANIZATIONSYSKEY], [SPEC_ID], [SYS_TYPE1], [TRIUSERMESSAGEFLAGTX], [TRINAMETX], [TRIIDTX], [TRIFORMLABELSY])
GO

CREATE INDEX [PERF_TRIPEOPLE6] ON [T_TRIPEOPLE] ([TRINAMETX]) INCLUDE ([SPEC_ID])
GO

CREATE INDEX [PERF_TRIPROJECTBUDGETCHANGE1] ON [T_TRIPROJECTBUDGETCHANGE] ([SYS_PROJECTID], [SYS_GUIID], [SYS_OBJECTID]) INCLUDE ([SPEC_ID], [SYS_TYPE1], [TRIDATEDA], [TRINAMETX], [TRIIDTX], [TRISTATUSCL], [TRISTATUSCLOBJID], [TRIUSERMESSAGEFLAGTX], [TRIREVISIONNU])
GO

CREATE INDEX [PERF_TRIPROJECTBUDGETCHANGE2] ON [T_TRIPROJECTBUDGETCHANGE] ([SYS_PROJECTID], [SYS_GUIID], [SYS_OBJECTID]) INCLUDE ([TRISTATUSCL])
GO

CREATE INDEX [PERF_TRIPROJECTORIGINALBUDGET1] ON [T_TRIPROJECTORIGINALBUDGET] ([SYS_PROJECTID], [SYS_GUIID], [SYS_OBJECTID])
GO

CREATE INDEX [PERF_TRIRECONTRACTFACT1] ON [T_TRIRECONTRACTFACT] ([TRIFACTACCOUNTINGTYPET]) INCLUDE ([TRIDIMORGANIZATIONTXOBJID], [TRIDIMPRIMARYUSETXOBJID], [TRIFACTTOTALCONTRACTRE], [TRIFACTTOTALCOSTNU])
GO

CREATE INDEX [PERF_TRIREPAYMENTFACT1] ON [T_TRIREPAYMENTFACT] ([TRICAPTUREPERIODTXOBJID]) INCLUDE ([TRIDIMCONTRACTTYPETXOBJID], [TRIDIMPAYMENTTYPETXOBJID], [TRIFACTOUTSTANDINGRECE], [TRIFACTOUTSTANDINGDAYS])
GO

CREATE INDEX [PERF_TRIREPAYMENTFACT2] ON [T_TRIREPAYMENTFACT] ([TRIDIMCONTRACTADMINISTOBJID], [TRIDIMISPAIDTXOBJID], [TRICAPTUREPERIODTXOBJID]) INCLUDE ([TRIDIMCONTRACTTYPETXOBJID], [TRIDIMPAYMENTTYPETXOBJID], [TRIFACTTOTALPAYMENTSNU], [TRISCOREONTIMENU])
GO

CREATE INDEX [PERF_TRISPACE1] ON [T_TRISPACE] ([SYS_OBJECTID], [SYS_GUIID], [TRINAMETX], [TRIIDTX])
GO

CREATE INDEX [PERF_TRISPACEALLOCATIONFACT1] ON [T_TRISPACEALLOCATIONFACT] ([TRICAPTUREPERIODTXOBJID], [TRIDIMSPACECLASSTXOBJID], [TRIDIMLOCATIONTXOBJID], [TRIDIMWORKPOINTFLAGLI], [TRIFACTALLOCWORKPOINTS], [TRIFACTALLOCAREAIMPNU])
GO

CREATE INDEX [PERF_TRISPACEALLOCATIONFACT2] ON [T_TRISPACEALLOCATIONFACT] ([TRICAPTUREPERIODTXOBJID], [TRIFACTALLOCMOVESNU], [TRIFACTALLOCWORKERSNU])
GO

CREATE INDEX [PERF_TRISPACEFACT1] ON [T_TRISPACEFACT] ([TRICAPTUREPERIODTXOBJID]) INCLUDE ([TRIDIMGEOGRAPHYTXOBJID], [TRIDIMSPACECLASSTXOBJID], [TRIFACTSPACEAREAIMPNU], [TRIFACTSPACEALLOCATEDA])
GO

CREATE INDEX [PERF_TRISPACEFACT2] ON [T_TRISPACEFACT] ([TRICAPTUREPERIODTXOBJID]) INCLUDE ([TRIDIMSPACECLASSTXOBJID], [TRIDIMLOCATIONTXOBJID], [TRIFACTSPACEAREAIMPNU], [TRIFACTSPACEALLOCATEDA])
GO

CREATE INDEX [PERF_TRISPACEPEOPLEFACT1] ON [T_TRISPACEPEOPLEFACT] ([TRICAPTUREPERIODTXOBJID]) INCLUDE ([TRIDIMSPACECLASSTXOBJID], [TRIFACTALLOCWORKERSNU], [TRIFACTALLOCAREAIMPNU], [TRIDIMWORKERTYPETXOBJID], [TRIDIMLOCATIONTXOBJID])
GO

CREATE INDEX [PERF_TRISURVEYFACT1] ON [T_TRISURVEYFACT] ([TRIDIMSURVEYTYPETX]) INCLUDE ([TRIDIMREQUESTCLASSTXOBJID], [TRIFACTRESPONSESCORENU], [TRIFACTMAXIMUMSCORENU], [TRICAPTUREPERIODTXOBJID])
GO

CREATE INDEX [PERF_TRISURVEYFACT2] ON [T_TRISURVEYFACT] ([TRIDIMSURVEYTYPETX], [TRICAPTUREPERIODTXOBJID]) INCLUDE ([TRIDIMLOCATIONTXOBJID], [TRIDIMREQUESTCLASSTXOBJID], [TRIFACTRESPONSESCORENU], [TRIFACTMAXIMUMSCORENU])
GO

CREATE INDEX [PERF_TRITASKDETAILFACT1] ON [T_TRITASKDETAILFACT] ([TRICAPTUREPERIODTXOBJID]) INCLUDE ([TRIDIMTASKTYPETXOBJID], [TRIFACTPREVENTIVETASKC], [TRIFACTPREVENTIVETASKS], [TRIDIMLOCATIONTXOBJID])
GO

CREATE INDEX [PERF_TRIWORKTASK1] ON [T_TRIWORKTASK] ([SYS_PROJECTID], [SYS_GUIID], [SYS_OBJECTID]) INCLUDE ([TRIMATRIXSERVICECLAS], [TRIMATRIXSERVICECLASOBJID], [TRIWORKINGLOCATIONTX], [TRIWORKINGLOCATIONTXOBJID], [SPEC_ID], [SYS_TYPE1], [TRINAMETX], [TRIUSERMESSAGEFLAGTX], [TRIIDTX], [TRISTATUSCL], [TRISTATUSCLOBJID], [TRIACTUALPERCENTCOMP], [TRIACTUALPERCENTCOMP_UOM], [TRIACTUALENDDT], [TRIACTUALSTARTDT], [TRIPLANNEDSTARTDT], [TRIPLANNEDENDDT])
GO

CREATE INDEX [PERF_WEB_LABEL1] ON [WEB_LABEL] ([APPLICATION_ID], [BO_ID]) INCLUDE ([LANGUAGE_ID], [LABEL_NAME], [LABEL_VALUE], [UPDATED_BY], [UPDATED_DATE])
GO

CREATE INDEX [PERF_WEB_MESSAGE1] ON [WEB_MESSAGE] ([LANGUAGE_ID], [USE_NAME])
GO

CREATE INDEX [PERF_WF_EVENT_HISTORY1] ON [WF_EVENT_HISTORY] ([COMPLETED_DATE])
GO

CREATE INDEX [PERF_WF_TEMPLATE1] ON [WF_TEMPLATE] ([STATUS_ID], [TEMPLATE_FLAG], [UPDATED_DATE]) INCLUDE ([WF_TEMPLATE_ID], [WF_TEMPLATE_VERSION])
GO

Reserve indexes

Tune Reserve indexes to include appropriate indexes for performance improvement. The following indexes were identified to help increase performance dramatically for reserve queries by the TRIRIGA performance team on SQL Server, but you must review and tune for your specific implementation.

CREATE INDEX [PERF01_TRIRESERVATIONINSTANCE] ON [T_TRIRESERVATIONINSTANCE] ([triPlannedStartDT], [SYS_OBJECTID], [SYS_GUIID], [SYS_PROJECTID])
GO

CREATE INDEX [PERF01_TRIRESERVATIONRESOURCE] ON [T_TRIRESERVATIONRESOURCE] ([SPEC_ID], [SYS_OBJECTID])
GO

CREATE INDEX [PERF03_TRIPEOPLE] ON [T_TRIPEOPLE] ([SPEC_ID], [SYS_OBJECTID])
GO

CREATE INDEX [PERF01_MYPROFILE] ON [T_MYPROFILE] ([SPEC_ID], [SYS_OBJECTID])
GO

CREATE INDEX [PERF01_TRIRESERVATIONDEF] ON [T_TRIRESERVATIONDEFINITION] ([SPEC_ID], [SYS_OBJECTID], [SYS_GUIID], [SYS_PROJECTID])
GO

CREATE INDEX [PERF01_TRIROLE] ON [T_TRIROLE] ([SPEC_ID], [triNameTX])
GO

CREATE INDEX [PERF01_TRICONTACTROLE] ON [T_TRICONTACTROLE] ([SPEC_ID], [SYS_OBJECTID], [ClassifiedByRoleSysKey])
GO

Lease indexes

Performance benchmark testing for Lease indexes was performed on the DB2® database platform. However, the findings from that platform might also apply to Microsoft SQL Server. Your database administrator can take the identified queries from the Db2 results and use the index advisor for your database platform to see which indexes are recommended on that platform.