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

SELECT
ind.name,
SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM
sys.indexes ind
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
INNER JOIN
sys.dm_db_partition_stats AS s ON s.[object_id] = ind.[object_id]
AND s.[index_id] = ind.[index_id]
WHERE
t.name = ‘INVENTTRANS’
GROUP BY ind.name
order by IndexSizeKB desc

The table data is stored in the TransOriginIdx

name	IndexSizeKB
I_177TRANSORIGINIDX	226992 ~ 221 MB
I_177OPENITEMIDX	63720
I_177STATUSITEMIDX	34312
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
where
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.