Smith & Nephew supports healthcare professionals in more than 100 countries in their daily efforts to improve the lives of their patients. Therefore their Dynamic AX system is shared by multiple business entity and requires very high performance. Prior tuning the database, we found that Dynamic AX creates the queries dynamically and all of them are Ad-Hoc basis therefore tuning such system from some other consultants are almost impossible.
Our consultant performed the Instance & Database Configuration, Storage and Database Re-Alignment, Database I/O Balancing, Database Indexing, Query Design and Execution Analysis services to make sure SQL Server is aligned with Dynamic AX requests and hardware resources are utilized wisely.
[warning]Note: Be aware that Dynamic AX database performance tuning is subjective and services required are different case by case.[/warning]
The following figure shows one of query performance prior our performance tuning service:-
and the following figure shows the performance improvement on the same query after our services have been done. SQL Server created a new execution plan for the same query due to database index and statistics changes to improve the performance.
The Dynamic AX database performance improvement for this specific environment was about 70 percent which in some system functions improvement reaches 100 percent. Base on customer observation, the time spend on regular operations from 15-20 seconds drop to 5-7 seconds which the current performance latency is caused by Dynamic AX engine as it uses lots of cursors to fetch and calculate data.