Performance Optimization by using Included Columns and Field Selects

Since version 2012, Dynamics AX supports included columns in indices although SQL Server supports it  for quite a long time. Here are some examples how and why it is good practice to use included columns in an index. I’m using Dynamics AX 2012 R3 Cu12 on Windows Server 2016 and SQL Server 2016 with Contoso Demo data for this example

Cluster Index.

The cluster index can be defined using multiple fields and is used to defined the order of records stored in the table. Even more important is the fact, that if a table has a clustered index all the data is stored in the table, i.e. the cluster index IS the table!


Take a look at the space allocated by the indices. About 219 MB are used to store actual data and 167 MB are used to store index information


The following SQL Statement reveals the size in detail

SUM(s.[used_page_count]) * 8 AS IndexSizeKB
sys.indexes ind
sys.tables t ON ind.object_id = t.object_id
sys.dm_db_partition_stats AS s ON s.[object_id] = ind.[object_id]
AND s.[index_id] = ind.[index_id]
order by IndexSizeKB desc

The table data is stored in the TransOriginIdx

name IndexSizeKB
I_177TRANSORIGINIDX    226992 ~ 221 MB
I_177ITEMIDX 24872
I_177RECID 23416
I_177DIMIDIDX 22192

Index Usage with Field Select

Here is an example of a select statement with field select on the InventTrans table

while select ItemId,DatePhysical
from inventTrans
InventTrans.ItemId == ‚0001‘ &&
inventTrans.DatePhysical >= str2Date(‚1.1.2011‘,123)

{ .. }

The trace parser reveals the actual SQL Statement sent to the database


What happens is what you would expect, SQL uses the ItemIdx for this query


Only 5 logical reads where necessary



Select Non-Index fields

When the query selects fields which are not part of the index, SQL server has to perform a lookup in the Cluster Index for each record identified by the ItemIdx to get all the other fields. For example the Voucher and Qty are not part of the ItemIdx.


213 logical reads were necessary to fetch the data


This can get even worse, when performing the lookup becomes to expensive. This can happen when the query returns a larger number of records. For example, when querying for another ItemId. In this example SQL server does not use the ItemIdx anymore, but performs a search in the clustered index instead. The ItemIdx became completely useless for this query.


SQL server required 1345 logical reads to fetch the data!



Included Columns

Since version 2012 Dynamics AX supports the definition of Included Columns for indices. These columns are not used to sort the index. These are just fields which are stored within the index to avoid costly lookups in the clustered index. In Dynamics AX you just add columns to the index and set the property IncludedColumn to Yes.


You can find the included columns in SQL server when viewing the properties of the index


When the statement from above is executed again, SQL server can use the included columns from the index and does not perform costly lookups in the clustered index.


Only 6 logical reads are required to fetch the data. This is a huge optimization compared to the 1345 reads without included columns.



Über erpcoder
Software Engineer and Dynamics AX enthusiast working in Research & Development for InsideAx

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

Du kommentierst mit Deinem Abmelden / Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: