The index trap: the indexes you are still paying for

Indexes are usually added for a good reason on a specific day: a report was slow, someone found the right column, the report got faster. What almost never happens is the second conversation, months later, about what that index costs now and whether anything still reads it.

How a key becomes a cost

In Business Central, every enabled secondary key defined in AL, in a table or a table extension, is maintained as an index on SQL Server. Microsoft's own guidance is direct about the trade: choosing keys is a compromise between the speed of reading data and the speed of inserting, modifying and deleting it, and it can be worthwhile to deactivate complex keys that are rarely used.

The SQL Server index design guide says the same thing from the database side: a large number of indexes on a table affects the performance of INSERT, UPDATE, DELETE and MERGE statements, because each index containing a changed column has to change too, and it recommends avoiding over-indexing heavily updated tables. Business Central's ledger and entry tables are exactly those tables. Every posting writes to them.

SumIndexFields add to this. SIFT is how Business Central makes FlowFields and totals fast, and it does so with SQL Server indexed views: one per enabled SIFT key, with the totals stored, and maintained by SQL Server whenever the base table changes. So every SIFT key is one more structure written to on every posting.

Reading the evidence without fooling yourself

SQL Server keeps a counter per index in sys.dm_db_index_usage_stats: seeks, scans and lookups by user queries, and user updates, the maintenance the index costs on every insert, update or delete. An index with many updates and almost no reads is pure cost on every posting run.

Two cautions before acting on it. The counters start empty whenever the database engine starts, and are cleared when a database is detached or shut down, so a week of uptime does not show the quarter-end report that runs four times a year. And reads that matter little in count can matter a lot in value: one month-end query may depend on an index that shows a handful of seeks.

The opposite tool deserves the same care. SQL Server's missing index suggestions are estimates made while optimizing a single query, before it runs; they are not tested afterwards, they can overlap each other, and Microsoft's advice is to review all the suggestions and existing indexes for a table and combine where possible rather than create each one.

Business Central now helps on both sides. From the 2026 release wave 1, an administrator can view index usage per company and turn off nonessential indexes, with unique indexes, primary keys and system indexes protected from being disabled.

A decision rule for each index

In our practice every index on a heavily written table ends up in one of three places, and the rule for each is simple:

• Keep it when something the business depends on reads it, even rarely, and you can name what that is.
• Combine it when it overlaps another index on the same table with the same leading columns; two near-identical indexes cost two sets of writes for one benefit.
• Disable it and observe when it is written constantly, read rarely and nobody can name its reader. Observe through a full business cycle before removing anything for good.

Ownership matters as much as usage. A key defined in an extension belongs to whoever maintains that extension, and the change belongs there too, not in a database script that the next deployment silently undoes.

The traps

• Adding every missing index suggestion. Each one was costed for one query, in isolation, and none was costed for the writes.
• Dropping an index because a week of statistics shows no reads. Check the uptime behind the counters and the calendar of the business first.
• Treating the append-only tables as harmless. Change logs, job queue entries and integration records grow quietly; an estate that was fine for years and then was not is very often one of these.

Five checks for this week

• For the three largest tables written during posting, list every index and its user updates against its seeks, scans and lookups.
• Note when the database engine last started, so you know how much history those counters represent.
• For each index with heavy updates and no reads, find which key or extension created it and whether anything is known to depend on it.
• Review any recent index created from a missing index suggestion against the existing indexes on the same table.
• Make the change reversibly: disable, observe through at least one full business cycle including month-end, then decide.

Veltheon is founder-led, based in Ontario, and works on Business Central and NAV, SQL Server and Azure. If one of these sounded like your estate, the first conversation is free and starts with the evidence you already have.