I am currently facing a problem with our AX 3.0 installation running with SQL 2000 DB (w/ SP4). Actually I do not administer AX but only the SQL Server. Now what happened?
Two weeks ago our AX dude together with some external expert did upgrade our AX 3.0 to SP6 (I think it was SP3 w/ KR 2 before). Since then users are complaining about slow query execution. Because I am the server guy, I need to fix it, of course. Now I already talked to the AX guy and also the external person about it and I was simply told that SP6 did some changes which would require the SQL Server to run on better hardware. Or to better seperate all the DBs and services.
I have to admit that currently everything runs on one machine: All 3-tier Axapta and DBs (also tempdb) on a single machine. Tempdb is at least on a different drive then the AX_DB. But before the upgrade we did not experience any such issues (about 40 concurrent users max).
No the question is: Does SP6 really change Axapta in such a way that it needs that much more performance (we have a AMD Opteron 248 and 4 GB RAM on the machine)? Or did the other guys maybe create the problem themselves during/after the upgrade?
If anyone could shed some light on the issue I would very happy.
Usually service pack upgrade enhances performance of the application. In this case, it is difficult to comment without knowing more details. Could you please provide us following info -
It is a generic statement. Therefore ask them for details of which specific changes are they talking about.
Again it is a generic one. Have you or some one identified which part of the application exactly that has performance issue?
On another note - I would suggest you to upgrade database to SQL Server 2005 with latest service pack. Or things permit, consider upgrading to Dynamics Ax 2009. Obviously if you decide to go for first option, you should try it out in a test environment first.
Just to clarify - the reason I suggested upgrading to DAX 2009 is because - unless you have special support agreement with Microsoft, they might not support your application. For more details, please refer here - http://support.microsoft.com/gp/lifesupsps
Can i make a suggestion that you run a SQL trace from the Axapta client, to determine whether the query is taking a long time to launch from axapta.
You can do this by loggin in AX - tools - options - SQL Tab - sekect SQL Trace - put threshold as say 100ms and write to a file location.
Then ask a user to do exactly the same, and you will see if AX is taking a long time to execute queries. Also might be useful to utilse SQL performance monitoring on the axapta client
Now I already talked to the AX guy and also the external person about it and I was simply told that SP6 did some changes which would require the SQL Server to run on better hardware. Or to better seperate all the DBs and services.