The Dynamics Ax Performance Team Blog gives a pretty good description why you want to add a recID-index for tables having created/modifiedDateTime property activated (on a SQL 2005 DB backend).
But you don't want to do this manually, do you?
Thought so. You can find the script I used to add these indexes here.
In short, here's what the script does:
- on each table in the AOT it checks whether the created/modifiedDateTime fields are active and the createRecIdIndex property is set not set
- for tables that meet these conditions: it creates a brand new index on recId
When a new index is created I'm also adding the modified/createdDateTime field. The Performance team blog states
When you Insert into a table with CreatedDateTime field on SQL 2005, the following SQL statement will be issued immediately following the Insert:
SELECT CreatedDateTime From %table% WHERE RecID=%recId% AND DataAreaId=%dataAreaId%
The same goes for the modifiedDateTime field with an update statement.
So my reasoning is to include the created/modifiedDateTime field right away in the newly created index.
This should enable SQL to fetch all required data using just this single index.
More and wider indexes slow down insert/update/delete operations: true ... but if this can speed up each and every insert/update, this may be a well-considered decision.