pdesjardins1
asked on
AX server performance improvement?
Microsoft Dynamic AX 2012 CU10
I have established a test environment in Azure, that mirrors our production environment.
RDS server - 4 CPU, 16GB memory, 50GB standard SSD drive
AOS server - 8 CPU, 32GB memory, 140GB standard SSD drive
DB server- 4 CPU, 112GB memory, 650GB standard SSD OS drive, 300GB Standard SSD DB drive, 100GB standard SSD TEMP DB drive.
AX performance is slow....
For example; clicking the Totals Button on the main call grid of a service call, ( should get you the parts, labor, and grand total of the whole call) can take up to 2 minutes for the total.
Shouldn't take more than 15 seconds.
For trouble shooting I increase the 'power' of all the servers.
RDS server - 8 CPU, 32GB memory, 50GB Premium SSD drive
AOS server - 16 CPU, 64GB memory, 140GB Premium SSD drive
DB server- 32 CPU, 256GB memory, 650GB Premium SSD OS drive, 300GB Premium SSD DB drive, 100GB Premium SSD TEMP DB drive. (made the needed changes in SQL to recognize the extra memory.)
AX user speeds did not improve.
Thoughts?
I have established a test environment in Azure, that mirrors our production environment.
RDS server - 4 CPU, 16GB memory, 50GB standard SSD drive
AOS server - 8 CPU, 32GB memory, 140GB standard SSD drive
DB server- 4 CPU, 112GB memory, 650GB standard SSD OS drive, 300GB Standard SSD DB drive, 100GB standard SSD TEMP DB drive.
AX performance is slow....
For example; clicking the Totals Button on the main call grid of a service call, ( should get you the parts, labor, and grand total of the whole call) can take up to 2 minutes for the total.
Shouldn't take more than 15 seconds.
For trouble shooting I increase the 'power' of all the servers.
RDS server - 8 CPU, 32GB memory, 50GB Premium SSD drive
AOS server - 16 CPU, 64GB memory, 140GB Premium SSD drive
DB server- 32 CPU, 256GB memory, 650GB Premium SSD OS drive, 300GB Premium SSD DB drive, 100GB Premium SSD TEMP DB drive. (made the needed changes in SQL to recognize the extra memory.)
AX user speeds did not improve.
Thoughts?
if you are using Mekorma as an add-on they recently released an updated version due to causing SQL to hang amd/or slow down while running processes in the background. i chased similar performance issues until Mekorma admitted their issue. Once upgaded on the AX server and local PCs all performance problems disappeared.
Is that the payment solution??
As this is a test system are you changing the GUID, server settings and flashing AUC files etc
As this is a test system are you changing the GUID, server settings and flashing AUC files etc
ASKER
Damon:
when you say ax2012 is this R2 or R3? - R3
how many users in your system? - 1
do you see any load on the SQL server? - No, SQL CPU 2~10%; memory 20%
which version of SQL are you using?? and patched to what level?? - 2014 Enterprise; 12.0.5571.0
are you running business logic in CIL?? - Business Intelligence? If not I will need further instructions.
how do statistics look?? - Servers or SQL?
when did you last re-index?? - Pretty sure we do it weekly
any performance tuning been done?? - Yes, we've run several performance test against or Production environment. Nothing conclusive was found.
when you say ax2012 is this R2 or R3? - R3
how many users in your system? - 1
do you see any load on the SQL server? - No, SQL CPU 2~10%; memory 20%
which version of SQL are you using?? and patched to what level?? - 2014 Enterprise; 12.0.5571.0
are you running business logic in CIL?? - Business Intelligence? If not I will need further instructions.
how do statistics look?? - Servers or SQL?
when did you last re-index?? - Pretty sure we do it weekly
any performance tuning been done?? - Yes, we've run several performance test against or Production environment. Nothing conclusive was found.
ASKER
Damon:
All three machines are a new build. Install a new instance of AX onto the machines. We then would do a restore of the Production DB to this test instance.
All three machines are a new build. Install a new instance of AX onto the machines. We then would do a restore of the Production DB to this test instance.
ASKER
Michael:
I will say no to Mekorma. I was part of this build from the beginning. I've never heard of this item.
I will say no to Mekorma. I was part of this build from the beginning. I've never heard of this item.
@Damon:
Yes, Mekorma is the payments module.
@pdesjardins1:
Understood, was just a thought based on my experience with that module.
Yes, Mekorma is the payments module.
@pdesjardins1:
Understood, was just a thought based on my experience with that module.
I mean database statistics.
I think the issue might be the system ID, when copying a production system there is a few tables you need to clean up in the database.
/********Delete old servers********/
DELETE FROM [dbo].[SYSSERVERSESSIONS]
DELETE FROM [dbo].[SYSCLIENTSESSIONS]
DELETE FROM [dbo].[SYSSERVERCONFIG]
/*******Update GUID for client cache*******/
UPDATE [dbo].[SYSSQMSETTINGS]
SET [GLOBALGUID] = '00000000-0000-0000-0000-0
I think the issue might be the system ID, when copying a production system there is a few tables you need to clean up in the database.
/********Delete old servers********/
DELETE FROM [dbo].[SYSSERVERSESSIONS]
DELETE FROM [dbo].[SYSCLIENTSESSIONS]
DELETE FROM [dbo].[SYSSERVERCONFIG]
/*******Update GUID for client cache*******/
UPDATE [dbo].[SYSSQMSETTINGS]
SET [GLOBALGUID] = '00000000-0000-0000-0000-0
ASKER
Damon:
Sorry, but I am not an SQL wizard.
Is there a way I can check before running delete commands?
Sorry, but I am not an SQL wizard.
Is there a way I can check before running delete commands?
ASKER
Damon:
Also, a command I could run in SQL to know my Logic Reads.
If you are feeling generous.
Thanks.
Also, a command I could run in SQL to know my Logic Reads.
If you are feeling generous.
Thanks.
-- When were Statistics last updated on all indexes?
SELECT o.name, i.name AS [Index Name],STATS_DATE(i.[object
i.index_id) AS [Statistics Date], s.auto_created,
s.no_recompute, s.user_created, st.row_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);
-- Helps discover possible problems with out-of-date statistics
-- Also gives you an idea which indexes are most active
SELECT o.name, i.name AS [Index Name],STATS_DATE(i.[object
i.index_id) AS [Statistics Date], s.auto_created,
s.no_recompute, s.user_created, st.row_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);
-- Helps discover possible problems with out-of-date statistics
-- Also gives you an idea which indexes are most active
-- Top Cached SPs By Total Logical Reads
-- Logical reads relate to memory pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads
AS [TotalLogicalReads], qs.total_logical_reads/qs.
AS [AvgLogicalReads],qs.execu
ISNULL(qs.execution_count/
AS [Calls/Second], qs.total_elapsed_time,qs.t
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stat
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
-- This helps you find the most expensive cached
-- stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure
-- Logical reads relate to memory pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads
AS [TotalLogicalReads], qs.total_logical_reads/qs.
AS [AvgLogicalReads],qs.execu
ISNULL(qs.execution_count/
AS [Calls/Second], qs.total_elapsed_time,qs.t
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stat
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
-- This helps you find the most expensive cached
-- stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure
ASKER
-- When were Statistics last updated on all indexes.
That query returned 19331 lines. Shall I put them in a text file?
That query returned 19331 lines. Shall I put them in a text file?
Email to Damon@annata.co.uk
ASKER
Update.
I fear that my testing of the server changes is incorrect.
After making the server changes I would boot up the AX environment and run my tests. I would do things like:
Opening Call
Creating Purchase Order
Deleting Purchase Order
Receiving Purchase Order
Creating estimate
Opening call w/ warranty
Totals button
Check segment/item details
and record the results...…
However, I was only running the tests once or twice between server reboots.
If I'm thinking correctly, that does not give SQL enough time to get items into cache.
Thoughts?
I fear that my testing of the server changes is incorrect.
After making the server changes I would boot up the AX environment and run my tests. I would do things like:
Opening Call
Creating Purchase Order
Deleting Purchase Order
Receiving Purchase Order
Creating estimate
Opening call w/ warranty
Totals button
Check segment/item details
and record the results...…
However, I was only running the tests once or twice between server reboots.
If I'm thinking correctly, that does not give SQL enough time to get items into cache.
Thoughts?
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
when you say ax2012 is this R2 or R3?
how many users in your system?
do you see any load on the SQL server?
which version of SQL are you using?? and patched to what level??
are you running business logic in CIL??
how do statistics look??
when did you last re-index??
any performance tuning been done??