D365 Index Creation

D365 Index creation is crucial for tables because it helps SQL retrieve data more efficiently. Without an index, the system must check each record in the table individually for a match, a process known as a table scan. When a table contains millions of records, this can take a significant amount of time. However, with a good D365 index, SQL can perform a seek operation, which finds records much faster.

Depending on the complexity of a SQL query, determining the correct index to add to a table can be quite challenging. This article will cover the basics of creating a D365 Index. As well as address some common misconceptions people might have when creating indexes without a strong SQL background.

Basics of creating a D365 index

First, let’s go over the key steps for creating a D365 index in Dynamics 365 Finance and Supply Chain (F&SC) using Visual Studio:

1. Navigate to the D365 F&SC AOT: Open the AOT (Application Object Tree) and then expand the Tables node.
2. Open the Table Designer: Start by opening the table designer for the table you want to create an index on.
3. Right-click the Index Node: In the table designer, locate the index node in the left pane.
4. Select “Create New Index”: Right-click the index node and select “New Index” from the context menu.

After naming the index, check the property setting for Allow Duplicates. If you want the new index to also provide uniqueness within the table, set this property to No.  Be cautious if you have existing records in the table, as creating a unique index could cause a scenario where duplicate records already exist and create database synch errors.

If you right-click the index name, you can select New Field to add a column to the index. Alternatively, you can drag and drop fields from the field list into the index.

The important properties to consider are the data field name and whether the field is an included column. We will discuss included columns in more detail later. For more information from Microsoft on creating indexes please view: Add indexes to tables through extension – Finance & Operations | Dynamics 365 | Microsoft Learn

Considerations when creating a D365 Index

Covering ranges on a query.

When creating a covering index, you’ll want to include columns used in the range of the query. A query range filters the data that the system retrieves from a table. Try to avoid using fields that are large in size, such as nvarchar(max) or long descriptions, as they can take up a significant amount of space in the database and are harder to search against. At some point, adding more columns to access a record yields diminishing returns. Furthermore, those added columns take up more space in the database. Therefore, I tend to limit the number of columns used to fewer than five.

A simple x++ query against the SalesLine table such as:

Select * from SalesLine 
where SalesLine.SalesID == _SalesId 
&& SalesLine.LineNum == _LineNum;

This query would have an index with the columns of both SalesId and LineNum to correctly cover the query ranges. The combination provides more uniqueness since there could be numerous records that match just on SalesId or LineNum.

Column order

The order of columns in a D365 index is extremely important: For the system to utilize an index on a query, the query must include the first column of the index as a range.  The system will utilize subsequent columns of the index to further narrow the result set, until a column no longer matches a query range. Note that the query does not need to use all columns of the indexes, except for at least the first one.

A good example I often see is users creating this simple x++ query against the DocuRef table.

Select DocuRef 
where DocuRef.RefRecID == _RefRecID 
&& DocuRef.Tableid == _TableID;

Reviewing the existing indexes on the DocuRef table we find the closest match as RefIdx.

Note how the first priority column for the RefIdx index is RefCompanyId. Since the query does not use this range, the system cannot utilize the index. Either we would need to add RefCompanyID to the query as a range, or create a brand new index with RefRecid and RefTableID as the first two columns. Also, notice that createdDateTime and createdBy are not ranges in our query, but the system can still utilize the index as long as RefCompanyId exists as part of the select statement. The following x++ query can correctly use the existing index:

Select DocuRef 
where DocuRef.RefCompanyID == curext() 
&& DocuRef.RefRecID == _RefRecID 
&& DocuRef.Tableid == _TableID;

Prioritize columns based on their level of uniqueness.

When adding multiple columns to a D365 index, prioritize the columns in the order that provides the most uniqueness. Place the more unique column, SalesID, first when adding an index to the SalesLine table with the columns SalesID and InventDimID.

Note: There are some exceptions, even in base Microsoft code.   An example would again be the DocuRef table, since refRecId is more unique than refCompanyID, that ideally would have been the first column for the index. Partition and DataAreaid fields would be another example and explained further below.

Attempt to re-use a D365 index for multiple queries

When creating indexes, aim to re-use them for multiple queries to optimize performance. Adding indexes to a table can increase database size and slightly impact insert statement performance. When you insert a new record, the system must update the indexes to include the new record’s values.

Where possible, add columns to the end of existing indexes, as long as the query utilizes all prior fields. For example, consider the following x++ select statements:

While select QueueTable where IsProcessed = 0;

While select QueueTable where IsProcessed = 0 && GroupID = 3;

You can create a single index to cover both queries by placing IsProcessed as the first column and GroupID as the second column.

DataAreaID and Partition fields

When you create a D365 index within Visual Studio, the system automatically adds Partition and DataAreaId as the first columns of the index. Tables marked as not company-specific are an exception to this rule, where the system will automatically add only the Partition column. Within SQL Management Studio (SSMS) you can view indexes on a table and see the Partition and DataAreaid fields with the following SQL query:

sp_helpindex <tableName>

Additionally, all queries written in X++ will have these ranges applied when converted to direct SQL statements. You can view the Partition and DataAreaId columns added as ranges to the query using tools such as Environment Monitoring, Trace Parser, SQL Profiler, or SSMS Activity Monitor.

The software handles most scenarios for DataAreaId and Partition automatically, but there are a few scenarios to be aware of. An example would be x++ queries with a crosscompany keyword function. If a user creates a customization to search for sales orders across all companies using the crosscompany keyword, such as:

select crosscompany SalesTable where SalesTable.SalesID == _SalesId;

This will generate a query without the DataAreaID range within it on the back-end SQL. Since all indexes start with Partition/DataAreaId, the system cannot utilize the index on a query with a range of just SalesID. In such cases, we would need to create a new index where we manually add DataAreaId as the last column of the index.

The system can now utilize the above index since it will order the columns as Partition, SalesId, and DataAreaId.

Note: In some unique scenarios, particularly in Dynamics AX 2012, where the database size can become quite large, you may need to have a more unique column above Partition and DataAreaId. This allows SQL to recognize it as the most efficient index to use.

Queries generated outside of the D365 x++ code

Since X++ handles adding Partition and DataAreaid to the back-end SQL calls, many forget to add these for direct SQL statements. It can also occur when users try to query a table within SQL Server Management Studio (SSMS). Unless you include DataAreaId and Partition in the queries, the system will most likely perform a table scan since it cannot utilize an existing index. This often takes significantly longer time to complete the query on large tables.

Included columns

SQL can utilize normal columns added to an index to search for records. However, you cannot use included columns for searching. Instead, the system includes them as extra data when it locates a record using the index fields. By adding columns as included columns, you can reduce the number of key lookups needed to obtain other informational fields for a record. In simple terms, the less the database has to lookup the complete record, the faster the performance will be.

The most common use for included columns is in covering indexes of the field return list. Suppose we create a new display method with the following x++ query:

SELECT SUM(Amount) FROM CustomTable WHERE CustomTable.ID == _ID;

In this case, we would create an index on the ID column, with Amount as an included column. This is because we are not searching for Amount, but including it in the index prevents the need for a key lookup on the records to retrieve the Amount column. 

I have also used included columns for ORDER BY statements and sometimes for relational joins. When using included columns, always review the execution plan to ensure that it improves the query’s performance.

Lastly, I have seen instances where SQL suggests a missing index and recommends adding all returned fields as included columns. In such cases, I typically ignore the suggestion for including all fields and try to limit the number of included fields to fewer than five. Adding every field in the table would increase the database size unnecessarily while providing little performance gain.

Testing a D365 index

From within SSMS you can turn on execution plans to see how SQL is retrieving data. You can do this by enabling the ‘Include Actual Execution Plan’ button on the top ribbon bar.

If we run the query in SSMS, a screen tab (next to the Results tab) will be visible for the execution plan. From this screen we can view key details about the queries execution.

From this view, we can see that CustTable is performing an index scan, which indicates that the system is not utilizing an index.

By hovering over the Select box on the far left side, we observe that the query cost is 36.969. The query cost is the value that SQL Server uses to estimate the resources required to execute a query. This cost is a combination of multiple factors, including CPU usage, I/O operations, and memory consumption.

Missing D365 Index details and re-testing

In the execution plan screen. we can also see some green text indicating a suggested D365 index. If you right-click it and select Missing Index Details, a window will pop up with a suggested index to create. Often, the missing index suggestion will have Partition/DataAreaId as the last columns of the index. To maintain uniformity with other D365 indexes and standard behavior, I move those columns to the beginning.

Verify that the system is utilizing the index after creating it. I often first test the index within SSMS before adding it to the code in Visual Studio. Indexes with suggestions above 80% are often useful, whereas the system may not utilize those below that threshold. Now we will run the query again including the DataAreaid field:

After we update the query to include DataAreaId and Partition, the system now utilizes an index seek. This indicates that the query is more efficient and leverages the existing index.

Hover over the table to see that the system uses the index AccountIdx. Also, under the Seek Predicates, the system utilized the Partition, DataAreaId, and AccountNum columns.

With the updated query utilizing the index, the cost has now significantly dropped to 0.0032. This reduction demonstrates the efficiency gained by using an index seek rather than a table scan.

Dynamics F&SC versus Earlier Versions

Lastly, let’s discuss some key differences between how a D365 index worked in previous versions such as Dynamics 2012 on-premise versus Dynamics 365 Finance and Supply Chain (F&SC).

Dynamics 2012 and Previous Versions (On-Premise)

• Indexes added through SQL Server Management Studio (SSMS) persisted until the system performed a database synchronization. After a database synchronization, the system drops all indexes not created by code in the application. Therefore, it was important to test new indexes within SSMS and eventually make them permanent though a code change.

Dynamics 365 F&SC

• You can add indexes directly in Dynamics 365 F&SC, and they will persist through database synchronizations and new package applications. There are a few methods for performing this. In sandbox environments, developers can add them through SSMS. In production environments, Microsoft usually adds them.
• Microsoft has automated systems in place that when a query is long-running, will sometimes identify missing indexes. The tool will then automatically add the missing index. Note: I prefer not to rely on this tool and there have been scenarios where I have seen it miss adding indexes to customizations.
• For production-critical issues, developers can create indexes through zero-downtime scripts.

To view indexes created in production and not through code, you can first perform a database refresh from production to a Microsoft-hosted sandbox environment From there, you can utilize just-in-time access to query the SQL database. The following query is useful for viewing all indexes along with their associated sizes:

select ind.name as [index_name],
ind.type_desc as index_type,
cast(sum(spc.used_pages * 8)/1024.00 as numeric(36,2)) as used_mb,
cast(sum(spc.total_pages * 8)/1024.00 as numeric(36,2)) as allocated_mb,
cast(sum(spc.data_pages * 8)/1024.00 as numeric(36,2)) as data_space_mb,
ind.is_unique,
ind.is_primary_key,
ind.is_unique_constraint,
schema_name(obj.schema_id) + '.' + obj.name as object_name,
obj.type_desc as type
from sys.indexes ind
join sys.objects obj
on obj.object_id = ind.object_id
and obj.type in ('U','V')
join sys.partitions part
on ind.object_id = part.object_id
and ind.index_id = part.index_id
join sys.allocation_units spc
on part.partition_id = spc.container_id
where ind.index_id > 0
group by obj.schema_id, obj.name, ind.name, ind.type_desc,
ind.is_unique, ind.is_primary_key, ind.is_unique_constraint,
obj.type_desc
order by sum(spc.total_pages) desc;

You can identify all indexes not created through code by their index name prefix. Those created from the application will usually start with the prefix ‘I_###’. As indexes can take up a significant amount of database space, it can be useful to review these automatically added indexes.

In conclusion

This has been a quick tutorial on creating a D365 index. I will explore more complex queries in future blogs, such as when it isn’t always obvious which index to add. Also, I will go over more detail on reviewing execution plans. For more information on troubleshooting query performance, please visit my other blog D365 Query Performance Utilizing the Query Store – My D365 Journey