Troubleshooting Series – Ep3 – Missing Indexes

As a third episode, I’d like to talk a bit about troubleshooting “missing indexes” in Business Central.  And as always – I just want to throw a bit of “waldo”-sauce on there, just to add my own perspectives, opinions and practices ;-).

What can we do for Business Central (I’ll focus on SaaS .. for OnPrem, there’s a whole set of tools / DMV’s you can dive into which is well described already) is described here on Microsoft Learn: Missing indexes in Business Central databases – Business Central | Microsoft Learn.  It’s a great place to start!

A few things that we need to keep in mind :

• The only way to add indexes, is through development.  So either an ISV can add an index on a table through an AppSource app, or a developer can add an index through a PTE app (either in its own table, or on a dependent table in a table extension).
• Having a lot of keys also adds a lot of “maintenance”, storage, .. which can have negative effect on write-heavy tables – so you’d only want to add indexes that are really necessary, not ones that are not used too many times.

Knowing that – let’s see what we have to work with.  So ..

What tools do we have?

Well – there’s not much.  We have the “Missing Indexes” page:

Which is basically just a representation of the DMV (Dynamics Management View) sys.dm_db_missing_index_details.

Now – typically about a dmv is that it loses its content after a server restart since they store data in memory.  And that’s also what happens for this dmv, like you see here, where I tracked the amount of missing indexes per day:

My guess is: when the database is moved from one server to another (for whatever reason – upgrade? Issues? Performance?), it loses its content.  This is a behavior we should take into consideration – the snapshot could be very old, or very new, both of which is to be considered.

Now, since the latest version, we do get a tad more stats that are really interesting.  They indicate how useful a certain missing index might be.  I mean – if it was missed only once – it’s not that useful.  But if it was missed a lot – it obviously is.  Just as an example…

In my opinion, the field “Estimated Benefit”, which is a calculation based on the other fields (Estimated Benefit = (seeks + scans) x (average total costs) x (average impact)), is most interesting to look at, and to be considered.  The higher, the bigger the benefit! 

Now – just imagine you could compare this over multiple customers…

What I did in the iFacto Telemetry app, I added some kind of way to track the missing indexes each day.  This gives you the possibility to create an overview (like the graph above) on missing indexes over time .. per customer, over multiple customers, per app, per table – whatever you like – with Telemetry.  Go nuts!

What tools do we NOT have?

The “Missing Indexes” is all we have, we don’t have any statistics on indexes: like which indexes are not used, or how useful are certain indexes – nothing like that.  Although if we would have a view on the dmv “sys.dm_db_index_usage_stats”, we could also figure out how many times existing indexes were used, and may be – for our own indexes – remove them if not used at all, to cut down on the index maintenance.  Wishful thinking for now .. and since it doesn’t have anything to do with AI – I guess wishful thinking for a long time..

Although – that also begs the question.  Would we be able to remove indexes?  What about …

Breaking changes

Are you able to do breaking changes regarding keys?  Well – I thought you couldn’t – but then I tested it- and it seems you can just remove (your own(!)) keys.  If I ask AI, it says it’s a breaking change, but when I try myself, well .. I have to conclude it isn’t.  I tried with setting up the AppSourceCop on the latest BC runtime, and it allows me to:

• Remove keys
• Change keys
• Add keys

I can even create keys that already existed – no clue what that does on SQL – and don’t care – let’s not “fix stupid” .

Though – be careful. You can conclude you don’t need it, but you might not take all data into considerations – apps may have dependencies, customers you don’t monitor might need keys you are removing.

Then again – we’re not able to analyze whether we should remove keys, we can only analyze whether to add new or not.

Some general recommendations

So here’s what we do.. (and what I think is recommended).

I guess we agree we should not create “too many indexes”.  All we have is stats on missing ones.  So “taking away indexes” is not something we can do – because we don’t have the stats.

And – the “missing indexes” page is just a snapshot of what has been missing since the last time the database restarted or set up.  It’s not a view over multiple months.. .

That’s why we follow up the “most missed indexes” in time by sending the snapshot of the missing index on a daily basis to Telemetry.  You can do this – iFacto Telemetry already does this. 

The benefit is: you can follow up the indexes on your tables, and see what indexes are most missed over multiple customers .. and start your analysis from there.  So we don’t “just” create the index – but let time tell which ones we need.

Good?  Not good?  Let me know

Original Post https://www.waldo.be/2025/08/29/troubleshooting-series-ep3-missing-indexes/