EnterWorks - ETT 136 - How to Find and Define Recommended Database Table Indexes Based on EnterWorks Usage

EnterWorks is a database-centric application, which means its performance can be affected by whether proper indexes are defined on its tables.  A previous EnterWorks Tech Talk (ETT 133 - How to Identify Missing Indexes Based on EnterWorks Data Model) lists indexes that should be defined based on the data model.  However, this doesn't reflect what indexes are needed but not defined based on usage.  A different stored procedure has been created that reports what tables would benefit by having indexes and for the Snapshot tables, the stored procedure identifies the repository and attributes by name. 

This session covers the usage of the stored procedure and best practices for defining the missing indexes.

Prerequisites - ETT 004

12/02/2023 - updated EW_Get_Recommended_Indexes.sql stored procedure to handle recommendation for snapshot table that includes a state field (vs. column with name like 'F_<attributeId>'), which was causing the stored procedure to fail with an error converting data type varchar to bigint.  Now the output will indicate a state field is included in columns for the snapshot table and the SQL provided to create the index must be used as there is no way to define an index within EnterWorks that includes a state field for a snapshot table.