Slow AX Performance

(0) Share

Report

Posted on by ITIPG

so i had an issue with our AX where it performance are quite slowpoke.

My Server:

VMWare6.0, Windows Server 2012 R2.

CPU 24 Core 2.4GHZ, RAM 512GB (500GB for DB & 12GB for System), SAN 4TB RAID 10, & current DB size are 1.9TB.

on heavy usage like Accounting Closing process, the RAM will keep rising until it hit 500GB & CPU usage are above 80%. Closing proses might takes 7-10 days & last month it getting worst to 16 days.

several thing that i had try:
1. i try adding more core to DB server from 12 core to currently 24 core but it seems the CPU usage are still high & the performance not improving much.

2. before we change to flash base SAN to improve our IOPS but it seems the performance are not getting better & monitoring the usage, it didnt even hit 10% of its total capable IOPS by it spec.

3. im experimenting doing closing the same data on DEV server. 1st closing my DEV server had 300GB RAM & 2nd closing im adding 200GB more RAM to become 500GB. the result are closing process are faster like 4%. still the same 7 days but just faster few hour. i conclude that additional RAM wont improve performance significantly either.

next, 2 different consultant advice me to move DB away from VM & suggest my DB server run on dedicated physical server.

so my question:
1. does moving DB Server to dedicated physical server would improve performance significantly.
2. if i would buy a new server for dedicated physical server, what spec recomended considering my current server spec.
should i get faster CPU frequency, or should i get more core, or should i get more RAM?

Thank you

All responses (12)

Answers (1)

Verified answer

Martin Dráb 233,305 Most Valuable Professional on at

Like (0)

Report

RE: Slow AX Performance

Index rebuilding can be (and typically is) done while users keep using the system. It would be a huge limitation if you had to shutdown your system every week, or how often you want to do it. Just note that the index isn't available when it's being reindexed, which can harm performance of queries that depend on it.

A common way for maintaining indexes and statistics is using a script from Ola Hallengren. You can easily set up parameters there, e.g. to reindex just indexes with high fragmentation.
ITIPG 90 on at

Like (0)

Report

RE: Slow AX Performance

Thank you for your response.

so, i try to contact my consultant again & he still insist that its dangerous.
considering my DB size & i only had 3-4 days at most for maintenance on upcoming holiday, im planning to partially reindex my table based on its size from the largest to the smallest.

as i never do it before on AX, im kinda doubt myself, should i do it or not. if not, i feel like acknowledge the slowness & live with it without doing anything. So what is the worst case that would happen if indexing progress not finished on day 5?

1. can my user still doing its activity but with slow performance due to indexing process?

2. if not, what would happen if i force stop the indexing process?

What risk that i might be facing if thing not going smooth as planned.

for now what i can planned are having full backup before execution & at worst if thing went bad, i just need few hour down time to restore my backup on day 5.

Thank you.
Suggested answer

TheGoodDBA 235 on at

Like (0)

Report

RE: Slow AX Performance

ITIPG,

Thanks for the additional information. You bring up some very interesting points here on SSD.

First, I'm glad you are using Enterprise edition, to make full use of the resources you have allocated.

In reply to 1 through 3:

1. The menu options in AX for synchronizing and re-indexing are ad-hoc use and not scheduled. I have always taken the approach of "creating" indexes in AX, but "maintaining" the AX-created indexes (rebuild/reorg) in SQL maintenance plans because they can be scheduled routinely and have no adverse effect on the index itself as it pertains to AX. You'll have to investigate how defragmented your indexes get in order to determine frequency by table.

2. I believe that even in the case of SSD, you want to keep indexes defragmented to minimize I/O page reads caused by splits. You can review this case study www.sqlskills.com/.../

3. Update Statistics is done in SQL Maintenance. If you rebuild an index, statistics are regenerated for you (auto-update stats). Otherwise, prepare a maintenance plan following reorgs for updating stats or use it ad-hoc.

Given the size of your system, you probably want to do some testing before rolling out any new maintenance plan. You might even want to rebuild one or two of your most fragmented indexes and see if it has a positive effect on the functions accessing those tables.

Cheers,

Steve
ITIPG 90 on at

Like (0)

Report

RE: Slow AX Performance

Thank you for your response

Yes, my SQL Server 2014 are Enterprise Edition.

So, this tickle me to find more on AX DB Maintenance where my 1st consultant forbid me to do any maintenance plan from SQL Server itself yet never mention maintenance via AX itself.

googling around found this one

1. Rebuilding index: it should be done through AX-->Administration-->Periodic-->Database-->SQL Administration. Select all the tables and select the option “Re-build indexes”. You can do the same from SQL Server but this is the recommended approach.

so my next question regarding this,

1. is doing it regularly is enough for maintenance plan? following it i saw 2 main action which is synchronize & indexing.

2. as mentioned, my Storage are flash based (SSD). does indexing still relevant as there is no fragmentation issue that could lead to the optical reader moving around to read scattered data on many place & not to mention defrag action could shorted SSD lifespan.

3. how to update statistic on AX? or it can be performed from SQL Server Maintenance Plan?

Thank you
TheGoodDBA 235 on at

Like (0)

Report

RE: Slow AX Performance

ITIPG,

Two responses:

One - What Edition of SQL 2014? There are 6 and only Enterprise edition will give you full use of the resources you have allocated to the server. www.mytecbits.com/.../sql-server-2014-editions

Two - Index maintenance is critical to performance. By maintenance I mean rebuilding and reorganizing the existing indexes to keep them defragmented and efficient. Followed by maintaining the statistics for the query optimizer to make efficient decisions. If you do not have some scheduled routine to maintain indexes, that is your number 1 problem.
ITIPG 90 on at

Like (0)

Report

RE: Slow AX Performance

What version and edition of SQL Server? There are resource limits to consider.

SQL Server 2014

My Configuration on SQL Server:

1. Memory Limit at 500GB out of 512GB. 12GB for Windows System

2. Processor, automatic for all processor

3. AX DB, set to autogrowth 200MB. currently 1.9TB.

4. TempDB, split into 8 file, each set to autogrowth 500MB & limited to 123.000MB.

Have you checked the status of your indexes and SQL maintenance routines?

I think this one is what i had never done.

before using AX, we develop program our self & use older SQL Server. that time even with small size DB i still do indexing & statistic maintenance routine monthly using SQL Server Maintenance Plan.

But my implementors consultant once told me not to do anything outside AX because it could messing up AX indexing as AX had its own way to do their indexing, so i had never run it. So maybe there is some maintenance plan inside AX that i dont know?

for note, currently our storage using Flash base Disk (SSD), so does indexing still relevant on SSD?

so far what i did on AX side are doing syncronize all table & full cil.

Thank you.
Community Member on at

Like (0)

Report

RE: Slow AX Performance

Is DB maintenance jobs are scheduled and running regularly without fail ?
TheGoodDBA 235 on at

Like (0)

Report

RE: Slow AX Performance

I agree with Martin that in almost every case we do not see the DEV and PROD environments on the same physical hosts, regardless of resources.

Also agree with Martin that SQL server will consume all of the RAM allocated to it eventually over time, and that it is not a cause for concern.

What version and edition of SQL Server? There are resource limits to consider.

Have you checked the status of your indexes and SQL maintenance routines? I've seen indexes and statistics become the main cause of performance issues like this, especially degradation over time and during intensive processing.

Instead of rebooting SQL in the event of degraded performance, only restart your AOS services and determine if performance is restored. If the performance of the system returns, you can rule out SQL as the "primary" performance issue, although it's rare to believe one thing is causing performance to suffer.
ITIPG 90 on at

Like (0)

Report

RE: Slow AX Performance

i think im missing some detail again that makes you confused again ,sorry...

currently i own 2 Physical Server running on VM

Physical Server1 run 3 VM in it:

1. Active Directory

2. VCenter

3. DB (PRODUCTION DB & REPORT SERVICES)

Physical Server2 run 3 VM in it:

1. AOS1

2. AOS BATCH

3. DEV (AOS DEV, DB DEV, REPORT SERVICES in one SERVER HOST)

for the problem itself,

on normal usage (no closing or other heavy task) from a fresh restart, it run fine until the Memory hit 97%. when this happen i would just restart SQL Services & Reporting Services & the performance would be back again. this happen every 5-7 days.

but on heavy task, mostly Accounting Task like Closing, Generate Financial report, DB CPU could jump above 80% constantly & Memory that normally full every 5-7days can be full just in 1-2 days.

when Memory are full, opening a menu like Purchase Order or Sales Order takes 1-2 minute to open. any other action also feel slower. but when CPU are full, it getting longer & sometimes posting Production Journal could takes 10 minute to post.
Martin Dráb 233,305 Most Valuable Professional on at

Like (0)

Report

RE: Slow AX Performance

The setup sounds a bit strange to me. Especially putting the production environment and the development environment on the server VM looks dangerous (my primary concern there isn't performance, but having them isolated would be better for performance as well). I'm also not used to seeing multiple AOSes instances for the same environment on a single server.

As far as I know, SQL Server tries to utilize all memory it has available, therefore the fact that it does doesn't necessarily mean any problem.

I'm not a DB specialist, but as far as I know, running SQL Server on a VM is quite common and it doesn't mean performance degradation if set up correctly.

In general, perfomance problems are difficult to fix without understand where the problem lies. Can you tell us more about the actual problem? You said that performance is bad, but what it actually means? Do you see it always or just in some cases? You mentioned Accounting Closing - is the process where you have the problem, or do you have a problem even if Accounting Closing isn't running (and therefore it's not really relevant)?