| title | description | author | ms.date | ms.topic | ms.author | ms.reviewer |
|---|---|---|---|---|---|---|
Missing Indexes in Dynamics 365 Business Central |
Description about missing indexes and database missing indexes page |
jswymer |
06/09/2022 |
conceptual |
jswymer |
jswymer |
Missing Indexes in [!INCLUDEd365fin_long_md]
This article gives an insight about missing indexes for administrators and developers. Database Missing Indexes will help administrators and developers to enhance the performance of their applications.
[!INCLUDEindexes_overview_md]
For more information on indexes and their types, see Clustered and nonclustered indexes described.
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 will help you in creating right indexes.
To learn how to use missing index suggestions to effectively tune indexes and improve query performance, go to Tune nonclustered indexes with missing index suggestions
To know more about Dynamic Management Views (DMV), see System Dynamic Management Views (Transact-SQL).
To understand how AL plays a part in efficient data access with SQL components, see Efficient data access.
Database missing indexes in [!INCLUDEprod_short]
To get information on missing indexes, go to Database Missing Indexes in Business Central, and you'll see 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. |
Important
The information provided on Database Missing Indexes page are the suggestions and must not be taken as mandatory actions. You need to analyze where and how many indexes are best suited for optimal performance of your application. Indexes also take storage space, can affect updates for the tables where insertions and deletions are more common, and therefore can be an expensive operation if you overdo it.
Performance Article For Developers
Optimizing SQL Server Performance with Business Central
How to tune indexes with missing index suggestions