Business Central Database Optimization: SQL Indexing Best Practices

Your users are not imagining it. When a posted sales invoice takes 40 seconds to open or a month end report runs for 20 minutes, the root cause is almost always the same: the database is working harder than it needs to. In our experience tuning dozens of Dynamics 365 Business Central environments, poor indexing sits behind the majority of slow list pages, sluggish posting routines, and reports that time out.

The good news is that indexing problems are fixable, and most fixes do not require a server upgrade or more licenses. They require understanding how Business Central talks to SQL Server and making deliberate choices about keys, SIFT structures, and query patterns. If your system has already crossed the line from slow to unusable, our Business Central support team can run a full performance audit, but if you want to understand what is actually happening under the hood, this guide walks through the indexing practices we apply on real client databases.

How Business Central Uses SQL Server (And Why It Matters)

Business Central does not let you manage the database the way a DBA manages a raw SQL Server instance. Every table, key, and index is defined in AL, the application language, and the platform translates those definitions into physical SQL Server objects. This has two big implications:

In Business Central online (SaaS), you never touch SQL directly. Microsoft manages the physical database in Azure SQL. All optimization happens through AL key definitions in extensions, query design, and telemetry analysis. You cannot open SQL Server Management Studio and add an index.

In Business Central on premises, you can touch SQL, but you mostly should not. Indexes added directly in SQL Server outside of AL metadata can be dropped during upgrades and schema synchronization. The supported path is still to define keys in AL so the platform maintains them.

Understanding this boundary is the first step. Everything below works within it.

1. Start With Evidence, Not Guesswork

Before adding a single index, find out which queries are actually slow. Business Central emits detailed telemetry to Azure Application Insights, including long running SQL query events (event ID RT0005) and lock timeout events. These traces show you the exact SQL statement, the AL object that triggered it, and the execution time.

A pattern we see constantly: a team assumes the Item Ledger Entry table needs more indexes, but telemetry shows the real offender is a custom report doing a full table scan on a bespoke staging table nobody remembered existed. Evidence first, indexes second.

For on premises deployments, combine telemetry with SQL Server's own tooling: Query Store, the missing index DMVs, and execution plans. Just treat SQL Server's missing index suggestions as hints, not instructions. They frequently recommend redundant or overly wide indexes.

2. Design Secondary Keys Around Real Query Patterns

In AL, a secondary key becomes a non clustered index in SQL Server. The rule that matters most: the order of fields in the key must match how your code filters and sorts.

If your warehouse code constantly runs filters like SetRange("Location Code") followed by SetRange("Bin Code") with sorting on "Posting Date", the key should be:

key(LocationBinDate; "Location Code", "Bin Code", "Posting Date") { }

A key with the same fields in a different order may be useless for that query. SQL Server reads composite indexes left to right, so a filter on the second field alone cannot seek efficiently on an index that starts with a different field.

Custom keys belong in extensions, and getting them right is a development discipline as much as a database one. This is exactly the kind of work our Business Central development team handles when tuning heavily used custom tables.

3. Use IncludedFields to Build Covering Indexes

Since the introduction of the IncludedFields property in AL, you can create true covering indexes: indexes that contain every field a query needs, so SQL Server never has to jump back to the clustered index to fetch the remaining columns.

key(CustNoDate; "Customer No.", "Posting Date")
{
    IncludedFields = "Amount", "Description";
}

For a report that reads customer entries in date order and displays amount and description, this single change can turn thousands of key lookups into one clean index scan. Covering indexes are one of the highest leverage optimizations available in Business Central because they cost little on writes compared to adding those fields as full key columns.

4. Treat SIFT Indexes as the Precision Tool They Are

SIFT (SumIndexField Technology) is what makes FlowFields fast. Behind the scenes, each SIFT definition becomes an indexed view in SQL Server that pre aggregates sums so that a field like "Balance (LCY)" on the customer card does not recalculate millions of ledger rows on every open.

Best practices we follow on every project:

• Only maintain SIFT on aggregations that are read frequently. Every maintained SIFT index adds write cost, because SQL Server updates the indexed view on every insert, modify, and delete of the base table.
• Set MaintainSiftIndex = false on keys whose sums are rarely used. The FlowField still works, it just calculates on demand.
• Watch SIFT on high volume transaction tables. Item Ledger Entry and Value Entry SIFT structures are common culprits in slow posting. If posting a large production order crawls, check which SIFT views fire on those inserts.
• Never stack redundant SIFT keys. Two SIFT definitions aggregating nearly identical dimensions double the write penalty for almost no read benefit.

5. Disable Indexes You Do Not Use

More indexes are not better. Every index on a table slows down inserts, updates, and deletes, because SQL Server must maintain each one on every write. On transaction heavy tables, over indexing shows up as slow posting, lock contention, and deadlocks during batch jobs.

AL gives you two properties to control this without deleting keys:

• MaintainSqlIndex = false keeps the key available for sorting in AL but drops the physical SQL index.
• Enabled = false disables the key entirely.

On premises, the index usage DMV (sys.dm_db_index_usage_stats) tells you which indexes have high writes and zero reads. Those are pure overhead. In SaaS, database insights in the admin center and telemetry serve the same purpose. Standard tables ship with keys designed for general use, and this is one of many areas where default configuration does not match every business, a theme we cover in depth in our piece on why out of the box Business Central fails for companies with non standard processes.

6. Write AL Code That Lets Indexes Do Their Job

The best index in the world cannot save badly written data access code. Three habits that consistently pay off:

Use SetLoadFields. Partial records tell the platform to select only the columns you need instead of the entire row. Combined with a covering index, this dramatically cuts I/O:

Customer.SetLoadFields("No.", "Name", "Balance (LCY)");

Filter before you loop. Applying SetRange and SetFilter so SQL does the filtering server side will always beat pulling records into AL and skipping the ones you do not want.

Avoid nested FindSet loops over large tables. A loop inside a loop over ledger tables multiplies query counts fast. Restructure with queries (the AL Query object) or temporary tables where possible.

7. Keep Statistics and Fragmentation Under Control (On Premises)

For on premises databases, physical maintenance still matters:

• Rebuild or reorganize indexes based on fragmentation thresholds (a common baseline: reorganize above 10 percent, rebuild above 30 percent).
• Keep statistics updated so the query optimizer picks sensible plans. Outdated statistics are a classic cause of a report that was fast last month and is slow today with no code changes.
• Size tempdb correctly and use multiple data files on multi core servers.
• Verify Max Degree of Parallelism and cost threshold settings follow current Microsoft guidance for Business Central workloads.

In the SaaS environment, Microsoft handles all of this automatically, which is one of the quiet operational benefits of cloud hosting.

8. Shrink the Problem: Archive and Clean Your Data

Indexes work on whatever data exists, and a table with 80 million rows of history from 2014 will never be as fast as one holding only the data you actually use. Business Central includes data administration tools for compressing ledger entries and deleting obsolete log tables (change log, job queue log, report inbox).

Companies that migrated from legacy systems often carry the heaviest baggage, because old transactional detail came across wholesale during migration. Deciding what history belongs in the live database versus an archive is a data strategy question, and it is one of the core principles behind the exact steps we use to migrate messy legacy data into Business Central without importing years of dead weight.

9. Test Index Changes Like Code Changes

Every key you add or disable is a schema change with real consequences. Our process on client environments:

1. Reproduce the slow scenario in a sandbox with production scale data.
2. Capture baseline timings from telemetry.
3. Apply the key change in the extension.
4. Re run the scenario and compare read performance and write performance. An index that makes a report 5x faster but posting 2x slower may be a net loss.
5. Deploy in a scheduled window and monitor telemetry for a full business cycle.

Index changes on large tables also take time to build during deployment, which can extend publish windows. Plan for it.

10. Know When Tuning Is Not Enough

Sometimes indexing is a bandage on a deeper wound. If the data model was designed badly, if customizations bypass standard patterns, or if years of quick fixes have left the system fragile, no amount of index tuning restores acceptable performance. In those cases the honest conversation is about a structural reset, and we have written candidly about when a Business Central reimplementation is the cheaper path compared to endless firefighting.

Quick Reference: Indexing Do's and Don'ts

Frequently Asked Questions

Can I add SQL indexes in Business Central online? Not directly. In the SaaS version, all indexes are defined through AL table extensions using keys, IncludedFields, and SIFT properties. Microsoft manages the physical Azure SQL database. This is fully supported and survives upgrades, unlike manual SQL changes.

Do more indexes always make Business Central faster? No. Indexes speed up reads but slow down inserts, updates, and deletes. On high volume tables like ledger entries, over indexing causes slow posting and lock contention. The goal is the minimum set of indexes that covers your real query patterns.

Why is my Business Central report fast in the morning and slow in the afternoon? Common causes include lock contention from concurrent posting, outdated statistics on premises, or background jobs competing for resources. Telemetry with Application Insights will show whether the slowdown comes from long running queries or lock waits.

What is a SIFT index in Business Central? SIFT (SumIndexField Technology) pre aggregates sums for FlowFields using SQL Server indexed views. It makes balance and total fields open instantly, but each maintained SIFT index adds write overhead, so unused ones should be disabled.

How do I find which queries are slow in Business Central? Enable telemetry to Azure Application Insights and monitor long running query events (RT0005), lock timeouts, and page load times. On premises, add SQL Server Query Store and execution plan analysis.

Get a Performance Audit Before Small Problems Become Outages

Slow ERP systems rarely fix themselves. They compound: users build workarounds, batch jobs overlap, and one day posting fails during month end close. A structured indexing review usually recovers most of the lost performance within weeks, without new hardware or licenses.

As a certified Microsoft Dynamics 365 partner, Trango Tech runs telemetry driven performance audits that identify exactly which tables, keys, and code paths are costing you time, then fixes them in a controlled, upgrade safe way. Connect with our team and get your Business Central environment back to the speed your business runs at.