EnterWorks - ETT 133 - How to Identify Missing Indexes Based on the EnterWorks Data Model
Data Model, Performance
EnterWorks is a Database-centric application and most operations result in one or more SQL queries being performed. As the volume of business data grows and the data model evolves, the performance of various EnterWorks operations may be degraded by lack of indexes on the EnterWorks snapshot tables, which present a relational view of the repositories that store the business data. While it is possible to identify what indexes need to be defined and then check to see which of those are missing, it would be tedious and error-prone by either missing indexes or mis-identifying indexes are needed.
A stored procedure has been created the extracts information from the EnterWorks data model, determines what database indexes should be defined, then compares this to what is defined, then produces a report of all the indexes including indicating which are defined and which are missing.
Prerequisites - ETT 004, ETT 097