Troubleshooting Series – Ep3 – Missing Indexes
Troubleshooting missing indexes in Business Central SaaS environments requires understanding the Missing Indexes page, which reflects SQL DMV data that resets after server restart. Since developers can only add indexes through apps, careful analysis using telemetry to track missing indexes over time helps identify which indexes are truly necessary before implementation.
"The only way to add indexes, is through development."
Key points
- Indexes can only be added by development, through an AppSource app or a PTE, not through configuration.
- The Missing Indexes page mirrors an in-memory SQL DMV that resets on restart; the author suspects it also resets when the database moves servers, so the snapshot may be very old or very new.
- Estimated Benefit combines seeks, scans, average cost and average impact, and is the field to prioritize.
- Sending daily snapshots of missing indexes to telemetry, as the iFacto Telemetry app does, allows trend analysis across customers, apps and tables.
- AppSourceCop allows removing or changing your own keys, but with no index usage statistics, removing indexes cannot be justified by data.
Quotes
- “The only way to add indexes, is through development.” (Clarifies that index management requires code changes through apps, not UI configuration, which impacts implementation strategy.)
Context
- Features: Missing Indexes page, Estimated Benefit calculation, telemetry tracking, index management
Source: waldo’s blog, community blog. Summary, key points and quotes are derived (CONTENT-NOTICE.md); read the original for the full text.