How to troubleshoot slow performance for Sage X3

:ECB]
[BCB:2:Database warning
CAUTION: These steps require knowledge of database engines and application databases (DBs) used by your Sage product (including Microsoft/Transact SQL, Pervasive SQL, or MySQL, etc.). Customer Support is not responsible for assisting with these steps and cannot be responsible for errors resulting from changes to the database engine or DBs. Before making changes, backup all system and application DBs required for a full restore. Contact an authorized business partner or DB administrator for assistance.
:ECB]
[BCB:3:Network warning
CAUTION: This solution requires advanced knowledge of your network. Contact your system administrator for assistance. Modifying Windows security incorrectly can severely affect system operations. Sage is not responsible for operation issues caused by incorrectly modifying your Windows security. Always create a backup of your data before proceeding with advanced solutions. 
:ECB]
[BCB:4:Operating system warning
CAUTION: This solution requires advanced knowledge of your computer's operating system. Contact your system administrator for assistance. Modifying your Windows Registry incorrectly can severely affect system operations. Sage is not responsible for operation issues caused by incorrectly modifying your Windows Registry. Always create a backup of your data before proceeding with advanced solutions. 
:ECB]
[BCB:5:Third-party support
CAUTION: Sage Customer Support does not provide assistance for issues related to third-party products or enhancements, hardware, report customizations, state or federal tax-related questions, or specific accounting questions. Please contact your Sage business partner, network administrator, or accountant for assistance. Please review this document for additional information on the scope of Sage Customer Support Services.e 

Performance issues can be caused by many things. There is not one solution for all instances of performance degradation. You must be able to narrowly define the issue. If it is determined to be a Sage X3 program issue, we will require steps to duplicate before we can resolve the issue.

Define the issue scope:

Are there any errors? Search for the error in:

• The knowledgebase (https://support.na.sage.com/)
• Sage City (https://sagecity.na.sage.com/support_communities/sage_erp_x3/f)

• What is the Sage X3 software version and patch level?
• Are you using a SQL Server or Oracle database? What is the version of that database?
• Is this the first-time occurrence? Has it been recent or gradual?
• Is the Sage X3 application server on a different virtual machine than the SQL server, and are both virtual machines on different host machines? Symptoms include all Administration menu items being fast and all other menu items elsewhere being slow (takes a few minutes to open). See KB Intermittent Slow Performance in all areas except Administration in v7 and higher (Syracuse/MongoDB).
• What changed?
• What are the users doing EXACTLY when it gets slow?

You may use attached document "Sage_EM_Architecture_Sizing_Questionnaire_ENG_v1.0.docx" to verify that the Hardware and Software versions are compatible and meets the requirements of Sage X3. This will also help in determining Environment issues noted in the next section below.

Environment:

Does your architecture correspond with the Sage X3 documentation?

• Was a new patch installed?
• A Windows update?
• Anti-virus?
• Have any new audits been activated?
• Is there any Statistical Parameters active?
  • Are they in real-time or scheduled?
  • Can they be disabled?
• New software added / add-on
• How was the data entered into the system? Front-end? Import? If import was it a custom template or standard? Other?
• Does the issue occur once, every time, or is it intermittent?
• Does the issue occur at the same time every day or week?
• What are the exact steps to duplicate?
  • What is the function name?
  • What is the menu path?
  • What is the series of clicks and events that lead to the issue?
• Has this area been customized?
• All users? Some users? One user?
• All data folders? One data folder
• Is the user using a V6 fat client?
• Does it occur on all web browsers?
• What resources are in use using Window’s resource monitor: https://www.youtube.com/watch?v=pLDP1bGlb7c?
• Determine if the pressure is coming from RAM, CPU, disk or network resources?
  • Is the CPU utilization coming from SQL Server? The application server? The Syracuse server? The print server? The Java server? Elsewhere?
  • What process in Windows is taking up the CPU?
  • How many CPU cores are allocated to this machine?
• Version 6 Architecture guide and system requirements
• Sage ERP version 7 Architecture Guide & software compatibility guide
• DocLink: Sage X3 Product Update 8 System Requirements and Architecture guide
• DocLink: Sage X3 U9 Architecture and System Requirements Guide
• Sage X3 V11 Architecture and System Requirements Guide

Some considerations for a virtual environment in KB ID 79722: DocLink: Can I deploy Sage X3 in a virtual environment?

• Performance analysis tools:

  • To help determine the quality of an infrastructure, Sage provides a test program to meter X3 performance by performing a set of data I/O operations to simulate some high-demand transactions. To test the X3 core level between runtime and DB only (no interaction with Syracuse, MongoDB, ElasticSearch, nor any other SAFE X3 component) review knowledgebase article ID 75064, DocLink: What is Sage X3 performance measurement tool?
  • Run a timing trace within a function to pinpoint the exact call of a subprogram within code: DocLink: Run Activation Timing trace utility. You may want to use run an Engine trace in conjunction with the timing trace or the Extended Events.
  • Microsoft tools can be used to capture statements that are sent to SQL Server and the SQL Server result sets from these statements. Sage support will likely request or work with you obtain a SQL Server trace and/or an Activation Timing trace with a performance issue.
    • If using SQL Server 2012 and later: Microsoft's Extended Events and X3
    • If using SQL Server 2008 and earlier: DocLink: Microsoft's SQL Server Profiler and X3
    • How to guide on tracing SQL processes
  • As of Product update 9, you can utilize the Flamegraph to analyze calls in a program: How to view performance analysis information (Flamegraph)

• General X3 Performance dependencies:

  Single-tier environment (X3 core level)

  Multi-tier (X3 core level):

  Typically, degradation between X3 core single-tier and X3 core multiple-tier (all other things being equal):

  About 40 to 50% more time with a 10 Gbps network link

  150 to 200% more time with a 1 Gbps network link

  Much worse with 100 Mbps network links

  Sage City Blogs

  These come from Sage City posts provided by some of our support folks that reference some tools and concepts that may help.
  https://sagecity.na.sage.com/support_communities/sage_erp_x3/b/sageerp_x3_product_support_blog/archive/2014/08/11/sage-summit-2014-explore-the-tools-available-for-troubleshooting-challenging-support-issues

  https://sagecity.na.sage.com/support_communities/sage_erp_x3/b/sageerp_x3_product_support_blog/archive/2014/08/11/sage-summit-bus-316-prepare-your-sage-erp-x3-solution-for-infinity-and-beyond-system-performance-best-practices

  https://sagecity.na.sage.com/support_communities/sage_erp_x3/b/sageerp_x3_product_support_blog/archive/2013/08/02/supporting-advanced-index-hints-from-sql-server-within-sage-erp-x3

  https://sagecity.na.sage.com/support_communities/sage_erp_x3/b/sageerp_x3_product_support_blog/archive/2014/09/26/improving-read-performance-leveraging-the-adxftl-parameter

  https://sagecity.na.sage.com/support_communities/sage_erp_x3/b/sageerp_x3_product_support_blog/archive/2013/07/17/solving-the-question-what-causes-my-tempdb-to-grow

  https://sagecity.na.sage.com/support_communities/sage_erp_x3/b/sageerp_x3_product_support_blog/posts/fixing-bottlenecks-using-sql-server-filegroups-and-tiered-storage

  • Physical server(s):
    • Power settings in the server's BIOS set to 'Balanced Mode' by default preventing CPU to run at max speed (Turbo Boost) for more than a few seconds. This should be set to 'High Performance' or equivalent depending on manufacturer.
    • Power Options in Windows is the default 'Balanced (recommended)'. This should be set to 'High performance'
  • CPU / Memory clock on runtime server
  • CPU / Memory clock on DB server
  • DB server heavily used by databases other than Sage X3
  • DB server storage poor performance
  • DB server database settings
  • Virtual server(s):
    • Virtualization hosts settings (Power management)
    • VM envelope parameters (Virtualization hardware accelerators off, CPU overprovisioning, etc...)
  • Network type: 100Mbs / 1 Gbps / 10 Gbps Note: The network throughput is not an issue by itself, it's the latency that the most important factor when doing multiple very small database operations from Sage X3.
  • All the factors above related to single-tier environment.

DocLink: Microsoft's SQL Server Profiler and X3
DocLink: How to troubleshoot performance in Sage X3? (Run Activation Timing trace utility)
DocLink: Microsoft's SQL Server Profiler and X3
DocLink: What is Sage X3 performance measurement tool
DocLink: Can I deploy Sage X3 in a virtual environment?
DocLink: Virtualization best practice documentation obtained
Attachment: 210_20230425124436_Sage_EM_Architecture_Sizing_Questionnaire_ENG_v1.0.docx