Missing indexes in Business Central databases
This article gives an insight about missing indexes for administrators and developers. Database Missing Indexes help administrators and developers to enhance the performance of their applications.
Indexes overview
Indexes are an effective feature in relational databases, like SQL, to speed up the process of finding data. By indexing your frequently needed data, the overall performance of your application is optimized. For example, when you need to see some information in your application, a database query will run behind the scene to complete your request and it will look up every record until it finds the required information. This process of finding information is time consuming and slows down the application performance. However, if you index the data, like those columns that are referred more often, the database will directly go to that column instead of looking up all records in the table. This will significantly increase the efficiency and overall performance of your application.
Learn more about indexes and their types in Clustered and nonclustered indexes described.
Missing indexes
When you run a database query, the query optimizer, which is an important database component, analyzes and chooses the best possible plan to complete the instruction. In doing so, it provides additional information about the ongoing operation that the operation might perform well if the particular column (or columns) is indexed. The SQL server's Query optimizer gets this information from Dynamic Management Views (DMV), in our case, sys.dm_db_missing_index_details. sys.dm_db_missing_index_details returns details about missing indexes, which help you in creating right indexes.
Learn how to use missing index suggestions to tune indexes and improve query performance in Tune nonclustered indexes with missing index suggestions
Learn about Dynamic Management Views (DMV) in System Dynamic Management Views (Transact-SQL).
Learn how AL plays a part in efficient data access with SQL components in Efficient data access.
View missing indexes in Business Central
To get information on missing indexes, open to the Database Missing Indexes page in Business Central and view the data in the following columns:
| Column | Description |
|---|---|
| Table Name | The name of the table on which the suggested columns are based. |
| Extension Id | The ID of your application to which this data is related. |
| Index Equality Columns | The data in these columns is based on equality queries. For example, “Select * from customer where id = 021” |
| Index Inequality Columns | The data in these columns comes from queries, which aren't based on equality operations. For example, Select * from customer where id < 200 |
| Index Include Columns | These columns have a copy of associated data for fast retrieval of information, which is based on the columns suggested in Index Equality Columns and Index Inequality Columns. Include columns aren't indexed columns themselves but point to the additional information linked to the indexed columns. For example, they include the fields in the Select part. |
| Seeks`* | Number of seeks caused by queries that could have used the suggested index. |
| Scans* | Number of scans caused by queries that could have used the suggested index. |
| Average Total Costs* | Average cost of the queries that would be reduced if the suggested index was added. |
| Average Impact* | Average percentage benefit for queries if the suggested index was added. The value means that the cost would drop on average by this percentage if the suggested index was added. |
| Estimated Benefit* | The estimated benefit of adding the suggested index is calculated as: (seeks + scans) x (average total costs) x (average impact). For example, consider a query that seeks 5 times, scans 10 times, and currently costs 10. If the impact drops 50% by adding the suggested index, then the benefit is (5 + 10) x 10 x 50. |
*Applies to 2025 release wave 1 (version 26.0) and later
Important
The information provided on the Database Missing Indexes page is a suggestion and isn't mandatory. Analyze where and how many indexes are best suited for optimal performance of your application. Indexes take storage space, can affect updates for tables with frequent insertions and deletions, and can be an expensive operation if overused.
Related information
Performance Article For Developers
Optimizing SQL Server Performance with Business Central
How to tune indexes with missing index suggestions