Dynamics Community Forum Thread Details

AX performance issue

Application : AX2012 R3 CU8 with latest kernel from Microsoft

SQL : SQL Server 2016

OS : Windows server 2016

I would like to understand that is it safe to install OS patches and SQL patches especially there is no support for AX2012 version now.
In production environment users were facing extreme slowness in Counting Journal creation and posting also overall slowness in Environment was experienced especially while opening Trade agreement, trial balance forms etc.
Post analysis it was found that there was a surge in counting creation than normal due to year end closing average monthly counting around 3000 and during peak 7000 journals were created thus there was high latency observed in underlying SQL
We found that underlying disc was able to support only 1000K IOPS per TB and issue was resolved by moving disc to higher tier supporting 3000k IOPS per TB and maximum traffic was coming from Counting Journal creation (Using On Hand option) and posting causing extreme slowness
It is a retail system generating around 200 mil sales transaction per year and at present size of the database is around 7 TB
What could be the way forward increasing the Disk IOPS as we move along with project or As the database will keep on growing From future perspective for better user performance especially in Counting area is it advisable to Archive tables involved and will there be any database inconsistencies and implication
Inventrans,
inventtransposting
Inventsum
Inventsumdelta
Inventjounraltrans
Inventjounraltable etc
Any scripts available to monitor AX application as per below requirement from SQL end
1. Long running queries with user id identification
2. Long running user session
3. Increase in version store size along with query causing the tempdb to fill with session id of user