BC Observatoryunofficial, made by waldo
community postPlatform, DevOps & Performancecommunity - not Microsoftreviewed - checked by Opus

Planning Table Indexes for the Best Performance

Explains the read versus write trade-off of table indexes in Business Central: clustered and non-clustered indexes, covering indexes with IncludedFields, SIFT views and columnstore indexes. Gives rules of thumb (each index may add 10 to 20 percent write cost, keep SIFT views to one or two on write-heavy tables) and shows how to check index usage with the debugger, execution plans, RT0085 telemetry and the missing index page.

Open in Claudehttps-stefanmaron-com-posts-planning-table-indexes-bc-performance--d30e727e97.md

post Stefan Maron

Stefan Maron · 2025-08-07 · 2,072 words · about 9 min

"Every index you add slows down writes. Insert, update, delete - all of them have to maintain every index on the table."
Show the post hereRead on Stefan Maron

Key points

  • Every added index increases insert or update time by 10-20% and can raise deadlock risk in multi-user environments; justify each index by confirming it’s actually needed and used
  • Clustered indexes define physical row ordering and are used by Get() and range queries; non-clustered indexes speed lookups on non-primary-key fields but BC limits you to 40 per table
  • SIFT views pre-calculate aggregations for fast reads but acquire range locks that cause deadlocks under concurrent writes; limit to 1-2 SIFT views on write-heavy tables
  • Columnstore indexes compress data and skip million-row segments but don’t support seeks or ordering in BC, making them suited only for large infrequent GROUP BY queries, not loop-based CalcSums
  • Use Query objects instead of FindSet for covering indexes because Query’s predictable SELECT clause makes truly covering indexes achievable, whereas FindSet adds unpredictable system and subscriber fields

Quotes

  • “Every index you add slows down writes. Insert, update, delete - all of them have to maintain every index on the table.” (This core tradeoff explains why indexes must be carefully justified; they solve read performance at the cost of write performance across all DML operations.)
  • “Reducing each to 1 SIFT view resolved the problem completely.” (Demonstrates that multiple SIFT views cause real-world deadlock problems in production; shows the direct impact of configuration choices on system stability.)

Context

  • Features: Clustered Indexes, Non-Clustered Indexes, Covering Indexes, IncludedFields, SIFT Views, Columnstore Indexes, Query Objects
  • Versions: v26.0, 2025 release wave 1

Source: Stefan Maron, community blog. Summary, key points and quotes are derived (CONTENT-NOTICE.md); read the original for the full text.