Missing Indexes in Dynamics 365 Business Central
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.
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.
For more information on indexes and their types, see 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 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 Business Central
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.
See Also
Performance Article For Developers
Optimizing SQL Server Performance with Business Central
How to tune indexes with missing index suggestions