Finance | Project Operations, Human Resources, ...

Why is AX Dropping Indexes?

(0) Share

Report

Posted on by Simon D

Hi,

I'm a SQL DBA with limited knowledge of AX.

I've added several indexes to an AX DB over the past couple of years and a handful of those are regularly deleted by AX (or at least the SQL account used by AX). It's a small proportion of the indexes that I've created and it seems that it is something specific about those indexes, as within a day or so of re-adding them, they'll get dropped again.

This is not following a DB synch, it seems to be at random times of the day, while AX is being used.

Has anyone seen this before or have any ideas as to why it's happening?

I have the same question (0)

All responses (18)

Answers (0)

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi Simon D,

Do you mean that some indexes manually created in DB are dropped randomly when synchronization is not performed? For the same tables or for random tables?

Was this reply helpful? Yes No
Simon D 10 on at

Like (0)

Report

Hi Sergei,

Thanks for the response. In the latest case, it's 2 indexes on 2 separate tables (1 index on each table). When the indexes are dropped, it's just those 2 indexes. There are other indexes on the same tables, and they're not dropped.

When I add the 2 indexes back again, they're dropped again within a day or so. They're dropped within a minute of each other.

I can't see anything else unusual happening at the same time on the server, certainly not a synchronization.

Simon

Was this reply helpful? Yes No
Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi Simon,

>> There are other indexes on the same tables, and they're not dropped.

Other manual created indexes? What are the table names?

Are you sure that someone doesn't run synchronization at the same time?

Was this reply helpful? Yes No
zhifeng on at

Like (0)

Report

Hi Simon,

That's your SQL version? There is an Automatic index management in latest SQL.

docs.microsoft.com/.../automatic-tuning

If your indexes are truly dropped, then some user or application or job is doing it.To find out who or what, you have various options:

write a DDL trigger to catch the DROP INDEX statements (and even disallow them from deleting your index). See Ken Kim's answer.

SQL Trace

enable Audit

use XEvents (Extended Events)

use SQL Profiler

read the transaction log

Was this reply helpful? Yes No
Suggested answer

Khurshid Wali 928 on at

Like (1)

Report

Hello Simon,

As a best practice, you have to create indexes on AX AOT. if you create custom indexes on SQL Db site AX. AX will ask you to drop the custom index during db sync because table did no match with the Db table.

If you don't want to work on AX AOT then you have to drop all the custom indexes before DB sync and create them after through scripts.

I would recommend you to move your indexes to AOT.

Note: you said "small proportion of the index" It is due to partial db sync. if you do full db Sync then it will throw an error for all custom indexes and ask you to remove all indexes.

regards,

Khurshid Wali

Was this reply helpful? Yes No
André Arnaud de Cal... 301,132 Super User 2025 Season 2 on at

Like (0)

Report

Hi Simon,

Were you able to move forward with the reply from Khurshid. He is correct. You have to maintain the indexes in the AX AOT on the tables.

It would be great if you update the question and let us know if your question has been answered.

Was this reply helpful? Yes No
Simon D 10 on at

Like (0)

Report

Hi Everyone, thanks for all of your responses.

As I said, I have a limited knowledge of AX, so I may be missing something obvious, though I did discuss this with our AX Development team before posting here. I'll try to clarify the problem based on your questions.

I'm sure that the indexes in question aren't being dropped due to a DB synch. I'm aware that indexes need to be added to the AOT else they'll be overwritten. But before I pass new indexes to the AX Team, I add them to AX for a few weeks to monitor their usage. Then when I'm confident that they'll be useful, I give them to AX who update the AOT. A DB synch is performed every few weeks when we apply an AX update

What we have here is a set of 7 indexes that I added to AX a couple of weeks ago. 5 are still in place but 2 keep getting dropped. They're on the ECORESPRODUCTIMAGE & DIRPARTYLOCATION tables. They're not the only indexes on those tables, but they're the only ones that I've added to those tables. They're being dropped within a day or so of me adding them, sometimes a few hours. The other 5 indexes are not being dropped.

I created an XEvents session to capture them being dropped. They're being dropped by the AX user within a minute of so of each other.

I've seen this before with indexes on other tables, which is when I created the XEvents session. The results were the same, I could see who dropped the indexes, when and from where. But I couldn't work out why.

I could now pass all 7 to the Development Team, so they'd eventually work their way through the system. But who's to say that these 2 wouldn't still be dropped, given that I don't know why they're being dropped now?

Simon

Was this reply helpful? Yes No
nmaenpaa 101,162 Moderator on at

Like (0)

Report

Do you mean that you added 7 indexes via AOT, and 2 of them are dropped in SQL? That would be something completely new to me.

Was this reply helpful? Yes No
nmaenpaa 101,162 Moderator on at

Like (0)

Report

Also, how can you be sure that nobody synced the database (or some tables) from AX?

Was this reply helpful? Yes No
Simon D 10 on at

Like (0)

Report

I added the 7 indexes directly into SQL, not via AOT. 2 of them keep being dropped. That's my normal procedure, I leave them in place for a while and when I'm happy that they're being used, I pass them to the DEV Team to update the AOT. If they've not sufficiently used, I drop them myself. I have now passed all 7 to the Dev Team.

However, we only synch the DB during an AX update, which happens every few weeks. The last update was over 2 weeks ago and I've created these indexes 3 times since. They've been dropped soon after on each occasion.

I've just checked with the Dev Team and they've confirmed that a synch has not been done since that last update.

Was this reply helpful? Yes No