Performance bottlenecks can occur at any level, so it's important to understand the different dimensions where things could be wrong and where to start. In a broad sense, there are three common factors that impact performance, as illustrated in the following image:
As described in Chapter 3, Infrastructure Planning and Design, the infrastructure plays a vital role in system performance. It is very important to select appropriate hardware for the various components based on the usage profile of the system. We also recommend an infrastructure design review to evaluate the hardware and software requirements during the infrastructure planning stage.
The following are the typical performance issues related to the hardware that are found in the customer AX projects:
There are a few tools that help you monitor performance for the hardware, which are explained as follows:
Hardware virtualization is the recommended option for Dynamics AX, as it reduces the cost of the initial setup and maintenance, in addition to providing high availability. The following are the common performance factors in a virtualized environment:
The following tools can be useful for viewing virtualization:
There are several environment and application-recommended settings that, if not done appropriately, can cause performance issues. The following sections explain a few issues at the high level.
Network bandwidth and latency plays an important role in the performance of the system. This becomes extremely critical when you have remote sites connecting to the Dynamics AX client. Make sure that the system requirements for network bandwidth and latency are followed as per the Dynamics AX system requirements recommendations.
It is highly recommended to use the Citrix or remote desktop/terminal services for WAN access. Enable compression by enabling the setting on the AOS configuration to minimize the size of the data packets between the AX client and AOS servers. Consider using other user-client performance options, such as disabling the fact boxes and the preview pane on the list pages, to improve performance.
Make sure to validate the following settings for Windows in the Dynamics AX environment:
SQL Server is the most important component of the Dynamics AX architecture. Most of the performance issues usually point to issues on SQL Server—setup or queries that are not optimized. Hence, proper configuration and recommendations need to be followed on SQL Server the optimal performance of the overall Dynamics AX application.
The following are some key considerations for SQL Server's optimum performance:
TempDB
heavily; make sure that the Microsoft recommendations are followed on the TempDB
storage and settings.TempDB
contentions using the Wait Stats technique.It is important to maintain your Dynamics AX solution with the most current releases and fixes available. Microsoft continuously releases hotfixes and kernel updates for performance issues as the customers come across them. You would rather run into issues that have already been reported by other customers and where the fix is available from Microsoft. During the project life cycle, keep looking for the important hotfixes/cumulative updates and evaluate if they can be applied. At least the kernel-level hotfixes should be applied, as they do not have any code upgrade constraint.
There are a number of settings and application configurations within Dynamics AX which can cause performance issues if they are not used appropriately. The following sections outline a few key configurations that require attention:
In Dynamics AX, the number sequences are used to create automatic sequences for documents and master data, such as customer account, voucher numbers, invoice numbers, and so on. A number sequence can be set to continuous or non-continuous. When using a continuous number sequence, gaps are not allowed, so a trip to the database is needed to get the next number. When the number sequence is non-continuous, you can allow preallocation per ID, and therefore reduce database calls and improve performance. Avoid using continuous number sequences unless required by the application.
Database logging is a feature in Dynamics AX that enables the logging of any data change when enabled for a table. This is a cool feature but needs to be used with caution, because it can cause a significant degradation in performance when used inappropriately. The following are some guidelines for using the database logging feature in Dynamics AX:
sysDatabaseLog
table regularlyDebugging is a great tool for Dynamics AX developers to troubleshoot the ongoing issues. However, it should not be enabled in the production environment. Even though you are not using the debugger in the production environment and it's just enabled on the AX Server Configuration utility, it can cause around a 10 percent performance degradation.
Database maintenance is important for any OLTP database. Having bad index maintenance or no index maintenance can severely degrade the performance of Dynamics AX. Many performance issues can be resolved if the appropriate database maintenance processes are put into place. The following are a few index maintenance guidelines:
Auto_Create_Stats
and Auto_Update_Stats
enabled. If you are running SQL 2008 R2 SP1 or greater, you can also enable the trace flag, 2371.We often find in customer environments that there is only one AOS instance for batch processing, and the setting on the AOS batch is the default setting with a maximum of eight batch threads. A good way to calculate the number of threads is to multiply the number of cores by two, but this depends on the processes running and should be validated in the testing phase. You can also set the user AOS to act as a batch server during the off-business hours when there are no user activities.
The code and queries used in the application logic can cause significant performance issues when the proper best practices and guidelines are not followed. The following are a few common areas where the code can cause performance issues.
As explained in Chapter 9, Building Customizations, data caching is an important property in the table. When the CacheLookup
property on the tables is not set correctly, it can cause performance issues due to an increase in the number of database calls. Just as an example, set the CacheLookup
property of CustParameters
to none and run a trace using the trace parser tool for the sales order invoicing process. You will notice a database call for CustTable
thousands of times. However, when the CacheLookup
property is set to EntireTable
, there will be one or zero calls to the database. This is a small but really important setting for performance. Follow the development best practices, and set the appropriate CacheLookup
property in the custom tables.
A code running on the wrong tier can cause too many RPC calls between the client and the server tiers and can cause significant performance overhead. Refer to the following guidelines to evaluate whether the code is being run on the appropriate tier:
TempDB
tables when you need to join them with the regular tablesIt has been discovered in code review or performance tuning that the business logic is often implemented using loops to manipulate the transaction data. When the number of records grows, the processes start slowing down due to the increased number of round trips between the database and AOS.
Dynamics AX provides the following set-based operations for data manipulation (INSERT_RECORDSET
, UPDATE_RECORDSET
, and DELETE_FROM
), which could be used as an alternative to the loops to complete the data manipulation in a single round trip.
However, in a few scenarios, these set-based operations are converted into row-based operations. Examples for such scenarios are as follows:
inMemory
temp tablesskip*
method is not useddelete_from
will turn into a row-based operation if skipDatabaselog
is not usedskip*
methods are not usedWe often hear the complaint that batch processes take too much time to complete. Often, the issue is that parallel execution or the resources available are not used effectively. The result is less throughput, longer response time, and inefficient use of all the hardware resources.
Depending on the nature of the workload and work, you can use the following three techniques for parallel batch processing:
Parallel processing can cause a deadlock and blocking in the system, but they can be taken care of by exception handling with the deadlock and retry mechanism.
The standard AX functionalities are a great source of information about how the batch framework can be best utilized in different parts of the system. The best example for this is the Data Upgrade Cockpit (read Chapter 14, Upgrade for more info on the Data Upgrade Cockpit), which uses multiple batch processing techniques for parallel execution.
Long-running queries are a very common cause of performance issues reported in Dynamics AX. You can often see long-running queries in the standard business logic, SSRS reports, and customizations; most of the time, this is due to missing or inappropriate indexes. It's important to understand that every business has different data composition and usage patterns, and the index usage depends on these factors. Hence, indexes need to be designed and optimized for the usage pattern of the business.
There are various way to monitor long-running queries. For example, you can monitor long-running queries in Dynamics AX itself by setting the AOS configuration client tracing option and the SQL trace option for Dynamics AX user options. You can also use the SQL Server DMVs or the DynamicsPerf tool to monitor long-running queries.
Using the display methods in Dynamics AX is a great way to show the calculated information on forms and reports. But using display methods can cause significant performance issues. Particularly when complex methods are used to calculate the values being shown on grids, a visible slow-down in form performance can be experienced. This is largely due to the fact that the methods are often run repeatedly with no apparent reason and the values are unchanged.
The performance issues reported by end users about a particular form taking a long time to open is quite common. Check if the form uses the display methods on the grid; this could be one of the reasons why the form is slow. Display method caching can be used to resolve this issue.