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:
- I/O contentions issues
- Low memory on the remote desktop servers
- Low memory on AOS
There are a few tools that help you monitor performance for the hardware, which are explained as follows:
- The Windows Performance Monitor: This monitors the performance counters for each of the component installed for memory, I/O, and the CPU to discover the overall hardware utilization and any potential bottleneck
- DynamicsPerf: The DynamicsPerf tool collects the SQL Server data from the Data Management Views (DMV) related to the memory, CPU, and I/O, which can be useful for identifying the hardware issues related to SQL Server
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:
- Overprovisioning (thin provisioning) for AX production servers
- There are no dedicated resource pools for the CPU and memory
- The memory balloon driver is not disabled (ballooning equivalent to dynamic memory)
- The use of storage thin provisioning (on-demand allocation of blocks of data)
- RSS (Receive Side Scaling) is not enabled (network traffic handled by CPU 0)
- Outdated Synthetic Device Drivers (for disk, NICs, and so on)
The following tools can be useful for viewing virtualization:
- Windows performance monitor: This uses the performance counter to monitor and discover the hardware utilization on the various server components in the Dynamics AX environment
- Third-party tools to monitor the virtual environments: Sometimes, the problem could be in the virtualization layer, and hence specialized software provided by virtual solution vendors can be used to identify any such issue
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:
- Verify that the SQL Server is configured to run as a background service in Windows. Ensure that the memory allocation is done correctly so that the OS has enough dedicated memory.
- Set the power plan to high performance (all AX servers).
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:
- Review the maximum degree of parallelism setting (ideally set to 1).
- Enable only the required network protocols—AX requires only TCP/IP. Any protocol other than TCP/IP can significantly reduce the overall performance of the Dynamics AX application.
- Disable hyper-threading on the SQL Server services.
- The Dynamics AX application uses
TempDBheavily; make sure that the Microsoft recommendations are followed on theTempDBstorage and settings. - Run performance tests and monitor the
TempDBcontentions using the Wait Stats technique. - Make sure that the best practices and recommendations are followed for the Dynamics AX database, which is available on the Dynamics AX Performance Team blog at http://blogs.msdn.com/b/axperf/.
- Implement the appropriate database maintenance processes, like reindexing and defragmentation.
- Apply the latest service packs for SQL.
- Update the firmware.
- Download and install the DynamicsPerf tool to periodically collect and monitor the performance data and take corrective actions.
- Ideally, do not put other databases on the same SQL instance (it may compete with the Dynamics AX database for the server resources).
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:
- Have a valid business reason for each database logging rule
- Only track what is needed (for example, track updates at the field level, insert/delete actions, and so on)
- For large transactions, do not use tables or the information that changes based on the transactions
- Purge or archive the
sysDatabaseLogtable regularly
Debugging 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:
- Reorganize the indexes that are larger than a thousand pages and are between 10 percent and 30 percent fragmented.
- Rebuild the indexes that are larger than a thousand pages and more than 30 percent fragmented using a fill factor between 85 percent and 95 percent, depending on the frequency of the job execution.
- It is also strongly recommended to run the update statistics regularly with a full scan, or with at least a 50 percent sample, as well as having
Auto_Create_StatsandAuto_Update_Statsenabled. If you are running SQL 2008 R2 SP1 or greater, you can also enable the trace flag, 2371. - Run the database maintenance script weekly or more frequently.
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:
- The code related to database operations or heavy calculations should be run on the server tier
- Minimize the interaction between the client and server tier by grouping the appropriate tier code into a method
- Send the information between the client and the server in a serializable format
- Use the
TempDBtables when you need to join them with the regular tables
It 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:
- When joined with the
inMemorytemp tables - When the database logging or alert is enabled for these tables and the
skip*method is not used - When the table has the delete actions defined;
delete_fromwill turn into a row-based operation ifskipDatabaselogis not used - When the table methods insert/update/delete are overridden, and the
skip*methods are not used
We 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:
- Batch bundling: In this technique, a static number of tasks is created. The work is split among these tasks by grouping the work items together into bundles. Each worker thread will process a bundle of work items before picking up the next bundle. For example, suppose you need to invoice 1,000 sales order invoices. To do so, create 10 tasks, and allocate 100 sales orders to each task. So, 10 sales order invoices will be posted in parallel.
- Individual task modeling: In this technique, a batch task is created for each work item. Here, you have a 1:1 relation between the batch task and the work item, and hence a more consistent workload distribution. For example, if you need to push the AX retail transaction data to 10 different stores, create 10 different batch tasks; each batch task will process the data for one store.
- Top picking: In this technique, you create a static number of tasks similar to batch bundling, but do not preallocate the work items. A staging table is created and populated with a list of work items to be processed. Each batch thread will pick the next available task and update the current one with the status In Progress. Each task will read the staging record data with the PESSIMISTICLOCK hint along with the READPAST HINT to get the next available work item without any blocking.
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.