Updated: 31 August 2023
Please note this document and all other documents it links to are living documents so will evolve over time as new things are discovered, new functionality is provided, best practises adjusted and/or when I get time to add content; so please make sure you come back and visit this source document often.
Contents
Performance tuning your Sage X3 system: Migration/Update
General blogs, KB articles, etc.
Introduction
When migrating to a separate server or doing an in-place upgrading, the performance “pain” point of the process is often where the table data gets re-written and updated to conform to the new versions table structures and new field requirements. This document aims to highlight areas where the time needed to complete a migration can potentially be minimised.
Preparing the source system
Any work you can do on the source system which reduces the time needed for the cut-over process will be a good thing!
Pre-migration steps
As described in the function prerequisites https://online-help.sageerpx3.com/erp/12/public/getting-started_requisites-for-upgrade-of-a-folder-v11&12.html it suggests the following activities:
- Run consistency verification
Navigate to Development, Utilities, Verifications, Data, Consistency https://online-help.sageerpx3.com/erp/12/staticpost/consistency-verification/ This will likely take a long time to run (several hours) but could help reduce the testing cycle by identifying data that may need fixing before the migration will succeed.
- Purge temporary tables
You should be doing this anyway, but certainly clearing out the temporary tables which could be significant sizes, would reduce the amount of data that needs to be handled. Refer to Time to tidy up? (Blog) if you need some help on setting up your purging.
In addition, you could also consider to:
- Archive transactional data
For large data sets, hopefully you already have implemented Archiving, which reduces the data set for the LIVE folder. The archived folder data can be transferred and updated as a separate exercise after the live folder data has been completed. Implementing archiving as part of a migration project could still be feasible but will add extra work to the pre-migration activities. This topic is also covered in Time to tidy up? (Blog)
- Check indexes and mitigate any changes
In general, more indexes will slow down writing data (as the indexes also need to be maintained) so reducing the number of any non-standard indexes could improve write performance, as there will be a lot of writing going on when you do the migration!
Run the check report on the tables to identify missing/added indexes https://online-help.sageerpx3.com/erp/12/staticpost/identify-missing-indexes/ Navigate to Development, Utilities, Verifications, Database, Search index (SQLDICO) This doesn’t take long to run and provides a report of any differences between the X3 data dictionary and SQL server index configurations. This can be useful to identify any custom indexes which may impact on processing time, which could perhaps be temporarily disabled, and any missing seeded indexes.
You could also check Parameters, Usage, Data, Database Optimisation for any enabled indexes to confirm if these need to be disabled for better performance.
Pre-migration scripts
There are two types of pre-migration scripts, Control and Pre-Upgrade procedures, located in the X3 folder of the source server. Both can be run well before any cut-over date so will not impact the cut-over timing. These are documented at https://online-help.sageerpx3.com/erp/12/public/getting-started_list-of-X3-upgrade-procedures-by-module-v12.html
Control Procedures
These check the data for consistency and for anything that may cause issues during the migration. They should only take a minute or two to compete, so are well worth running just to see of anything is highlighted for attention. Run the Control procedures for each folder to be migrated. To run all these control scripts in one go, use the script file UUMGCTLX3 in the X3 folder (Development, Utilities, Miscellaneous, Run Process) For example, the resulting report may highlight records in GACCTMP or MTCBATCH (which should be cleared) or SORDER records which do not have corresponding SORDERQ record, etc.
Pre-upgrade procedures
These scripts claim to significantly reduce the cut-over processing time by updating tables used in the migration process, but only add NEW data in NEW columns, so will be ignored by the earlier version of X3. This means these scripts can be run any time before the cut-over date on the LIVE folder but bear in mind will take system resources and lock tables for a period of time, so may wish to run outside normal user working hours. To run all the pre-migration scripts, use the script file UUMGPREX3 from the X3 folder.
NOTE: In my own testing I did not find any time benefit from running these scripts and looking at the source code they do not seem to have been kept up to date which may explain why this is the case. Overall, I would have to suggest it is unlikely to give any benefit to run these Pre-migration scripts so would not recommend running them at this time.
Data export
When it comes to the actual physical export of the source live folder data, as discussed in blog “Reducing the time taken exporting/importing folder data via the X3 Console” https://www.sagecity.com/gb/sage-x3-uk/b/sage-x3-uk-support-insights/posts/how-to-reduce-time-taken-exporting-importing-folder-data-1916367834 using the Native Dump Files option will likely significantly reduce the time needed to export the data from the LIVE folder, but also reduce the importing time as well on the TARGET server.
For example, my slightly modified SEED folder data with the standard export to SVG took 40 minutes to complete, but when using the Native Dump option took only 8 minutes!
If you do not have the required X3 Console version on the source system (X3 Console 2.46) you can generally upgrade to this version (unless you are using X3 V6) If you are nervous to do this on an old live system, you can install the later X3 console version as a different user account (or different machine entirely) then import the solution.xml. Then you can use the new version only for creating the SVG export.
Preparing the target system
System monitoring
If you don’t already have your own Windows server/SQL monitoring tools available, you may wish to consider installing and configuring the Sage “Investigation Scripts” and have these running during your Migration test runs. Although it does introduce a small overhead, in the case of performance issues being encountered during your test runs, as you will have performance data collected before, during and after the process this should give you a good chance to review the data and understand root cause without having to run the whole thing again. These scripts are described in several of the “Sage X3 Technical Support Tips and Tricks” sessions, for example in the September 2021 session.
Sage X3 setup considerations
There are several areas where upgrade performance could be influenced by the Sage X3 setup:
X3 Folder parameters
Connect to the X3 folder then navigate to Parameters, General Parameters, Parameter values (ADPVAL) Check the chapter SUP (Supervisor) and group PRF (Performance) for the following settings:
“Update transaction limit” (MAXUPDTRS) https://online-help.sageerpx3.com/erp/12/staticpost/update-transaction-limit/ This parameter can have a HUGE impact for larger tables. Counter-intuitively, setting this value higher could cause a big slowdown in processing time for large volume tables. For example, in one case found that upgrade scripts UUMGTRTSAL02, UUMGTRTSAL06 and UUMGTRTSAL08 took 2.5 days to complete when this parameter was set to 50’000, but only took 2 hours when set to 5’000.
For my specific test data set*, I found setting MAXUPDTRS to 1000 gave me the best performance, beating lower settings by around 20% and beating higher settings by around 10% (tested with 100, 500, 1000, 2000, 5000, 10’000, 20’000, 40’000, 60’000 and 100’000 values) *My test data is based on SEED folder but with additional data added. Highlights of which are: SORDER table 64’000 rows, PINVOICE 47’000 rows, ORDERS 598’000 rows, GACCENTRYD 166’000 rows.
“Sync launch after patch integration” (LAUNCHSYNC) https://online-help.sageerpx3.com/erp/12/staticpost/sync-launch-after-patch-integration/
The online help states “This parameter is used after completing the integration of a patch list.”
“Yes” is the default setting, however this parameter does not apply to running the folder validations when doing an Upgrade/Migration run, so there is no need to change.
Although not related to performance as such, I would also suggest to set "Number of lines in memory" (NBTRABUFF) to 5 rather than 50 for the first test run, to flush the log files more frequently. If the process "hangs" you will at least be able to see where it has actually got stuck on.
Parallel Jobs (X3 solutions) https://online-help.sageerpx3.com/erp/12/staticpost/sage-x3-solutions/
The online help says “Indicates the number of jobs that can run in parallel during the update process. The 0 default value indicates that there is no limitation.” which is not all that descriptive.
To check/change this value Navigate to Administration, Endpoints, X3 solutions. In the “Runtimes” section, you can see the “Parallel jobs by runtime during update” value.
This parameter is reputedly only used when processing multiple folders in parallel, to limit the number of concurrent upgrade processes. In my testing on a 4-core server processing only one folder, I found that any number other than 0 ran around 15% slower (tested with value of 0,1,2,4 and 8) so seems leaving as the default 0 value is best bet unless you are processing multiple folders and need to limit the number of processes running.
Parallel Launches (Sequencing monitor) https://online-help.sageerpx3.com/erp/12/staticpost/sequencing-monitor/
If you do not create your own entry, the migration process will create one automatically and assign “No. of parallel launches” to a value of 1. The maximum value for parallel launches is 9 which is what I would recommend you use, by creating your own entry before launching the migration.
Navigate to Usage, Migrations, Sequencing monitor and create a new entry. Your Plan name MUST be the same as your folder, otherwise it will be ignored. Set “No. of parallel launches” to 9 and then save the entry. With this setup, when the migration is launched and gets to the migration plan part of the process, it will then be able to launch up to 9 processes in parallel.
These processes run within the Batch Server, so you must also check/change the batch server is configured to process enough queries. Navigate to Administration, Endpoints, Batch server and check/change the “Maximum active queries” to an appropriate value. For example, if you configured to run 9 migration scripts in parallel, enter a value of at least 10, assuming this won’t have a negative impact on system resources. For my testing, setting parallel launches to 9 improved performance by 33%, albeit it only took 15 minutes for this phase to complete on my data with value of 1.
Elastic Search
Elastic Search process can take a lot of RAM during normal running and the index update can take a lot of CPU when it runs. For best performance, if Elastic Search is running on the same server as the X3 Runtime, then shutdown the Elastic Search service and check there are no index update jobs scheduled (Administration, Usage, Automation, Scheduler) whilst running the migration process.
Recurring Batch Jobs
Check for any recurring batch jobs scheduled to run during the migration window. Navigate to Usage, Batch server, Recurring Task Management. If these can be deferred or disabled during the migration processing, this will make sure as many server resources are available as possible to reduce the time for the migration processes to complete.
SOAP Pools and Web Service child processes
Are any SOAP pools configured? If so, can these be disabled whilst the migration is running? Doing so will reduce the load on the runtime and database by a small amount. Navigate to Administration, Administration, Web services, Classic SOAP pools configuration.
Similarly, if Syracuse is running on the same server as the X3 runtime, then setting Web Service child processes to 0 would reduce the CPU/Memory being used which can then be utilised by the migration process. Navigate to Administration, Administration, Servers, Hosts.
SQL Server setup
The workload of SQL server is very different when running lots of big data updates during the migration compared to the normal user working. Assuming SQL server is not being used for other things, you could consider tuning SQL Server differently for the duration of the migration, then reverting to normal running configuration afterwards.
Would suggest considering the following areas, in conjunction with your local SQL Server expert:
- SQL Server parameters “Maximum server memory”
If SQL server is on the same server as X3, you could give SQL server more memory than normally recommended. Although X3 processes will be busy, it will only be on a few processes, whereas SQL Server will be doing a lot of the heavy lifting!
- FULL vs SIMPLE recovery model
Review the SQL server documentation Recovery Models (SQL Server) for explanations if needed.
It is tempting to think that switching SQL server from FULL to SIMPLE recovery model would make SQL faster, as it doesn’t have to backup the logs in SIMPLE mode. SQL Server will always track the transactions, the difference between FULL and SIMPLE is how the transaction logs themselves are handled. There is no reason for there to be a performance difference between FULL and SIMPLE, provided you have your SQL database sized correctly and the backing up of the logs configured appropriately for your load. My own testing supported this conclusion. However, if you have large data volumes you may find that the SQL logs are flooded and cause the upgrade process to fall over. Sage therefore recomend you perform your migrations using only the SIMPLE mode.
- SQL Server Agent jobs
Review any SQL Server agent jobs that are scheduled to run during the migration window. If none are needed you could just stop the SQL Server Agent service until the migration is over, otherwise disable any jobs that are not essential for the system to run. This will prevent any automated SQL Server jobs from running which may impact on the performance of the migration process.
Windows Server setup
- Windows updates
Consider to “Pause updates” just before you launch your migration to ensure it doesn’t kick in during your migration process.
Either:
Navigate to Server Manager, Local Server, Windows Update
Then select “Advanced options” and set the “Pause updates” option to “On”
This pauses updates for 35 days
Or:
Launch Command Prompt with “As administrator”
Run SCONFIG and select option 5, then “Manual”
- Anti-Virus
Ensure your anti-virus is setup correctly to exclude the Sage directories/processes, per our recommendations in the Architecture Guide (Technology section) so that Anti-Virus software is not hogging CPU or otherwise slowing down Sage X3.
General blogs, KB articles, etc.
Build diaries (Blog)
Time to tidy up? (Blog)