Re-index from AX or SQL

(0) Share

Report

Posted on by rudra

6,534

Please clarify Re-indexing from AX SQL administration form will increase the performance or Re-indexing from the SQL database server will increase the performance.

1. When Re-indexing from the AX SQL administration form my AX getting hang for a while and then getting bootup.

2. Re-indexing from SQL database server will not make my application to get hang.

3. When i create any index in SQL server, then again if i run re-index from the AX form and database sync all the newly created index via SQL server will be deleted. Is this not supported in vice versa.

Any help is really appreciated!!!

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Verified answer

Martin Dráb 237,908 Most Valuable Professional on at

Like (1)

Report

Synchronization ensures that the DB schema corresponds to the definition in AX. Because your indexes aren't defined in AX, they're dropped. Add them to AOT and you'll be all right. The only other option would be recreating them after every synchronization.

Reindexing from AX calls the rebuild command in SQL Server, therefore you can do exactly the same thing outside AX. Doing it at DB-level gives you more options and you can easily add it to your DB maintenance plan.

AX hangs because it calls the rebuild synchronously.

Was this reply helpful? Yes No
rudra 6,534 on at

Like (0)

Report

Hi Martin,

When we creating the index through sql it will not update in AX bcoz vice versa its happening.

As you said corresponds to DB schema and definition in AX synchronization drops the created index through sql, is it possible to change the DB schema and the definitions in AX

Was this reply helpful? Yes No
Martin Dráb 237,908 Most Valuable Professional on at

Like (0)

Report

Again, you can only add indexes in AX. If you created them directly in database, the synchronization will delete them. Do it in AX.

Was this reply helpful? Yes No
Dick Wenning 8,705 Moderator on at

Like (0)

Report

create the index first time by synchronizing the DB from AX

from that time it is up to the maintence tasks of SQL

you have rebuild and reorganize, a rebuild will also update the statistics.

also with rebuild from SQL you can influence the fill factor

Was this reply helpful? Yes No
Suggested answer

rudra 6,534 on at

Like (0)

Report

@ Martin , so we can conclude it by creating the index in AX and sync the DB as well as per the best practice and rebuilding index from the SQL maintenance plan is best way as well.

@ Denis , i have set the Auto Statistics to be True for my Ax database, so this will be updated instantly.

Thanks to all for the timely responses.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi, Martin.

Question:

Table DimensionHierarchyLevel hung on trying to open both as from AOS and from SQL. On it, Opening transactions action hangs (defined by debug). Does re-index will help?

Thanks

Was this reply helpful? Yes No
Martin Dráb 237,908 Most Valuable Professional on at

Like (0)

Report

Reindex might help. I can't say for sure from this information whether your problem is caused by an index, a lock or something else.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Is there any command line Parameter to reindex DB from AX ?

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

No, but you could create your own. Refer to SysStartupCmd* classes.

Was this reply helpful? Yes No
Martin Dráb 237,908 Most Valuable Professional on at

Like (0)

Report

Nevertheless the right approach is utilizing maintenance plans directly at the database server. It's easier and configurable, it has a scheduler and you'll be able to use a consistent approach across all databases.

Was this reply helpful? Yes No