Updated: 11 January 2023

Back to Index

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 Sage X3: Database – SQL Server

Introduction

Information to gather

General blogs, KB articles, etc.

Introduction

The database performance is key to good X3 performance.  The first step would always be to use  the X3 Performance Index Tool (described in X3 specific considerations) but where do you go after that?

Information to gather

Information to collect would include the following (script names mentioned below refer to the X3 Support team “Investigation Scripts”):

• Exact SQL Server version being used
  Are you using latest versions and patches to get best performance and stability.

• What server level backups are taken and on what schedule (e.g. VM snapshots, as well as SQL Server level backups)
  Can backups be hitting performance during user working hours.

• How many SQL instances and databases are hosted on this server
  Could other non-Sage X3 databases be contenting for resources (e.g. SEI)

• Is the database in Full Recovery mode or Simple mode (Included in output of the “mzBasicSQLServerInitialData.sql” script)
  If using Full Recovery Mode, are the transaction logs sized correctly and being backup up quickly.

• What jobs are scheduled using the SQL Server Agent (Output of the “mzSQLServer_Jobs.sql” script)
  Are there any heavy hitting jobs running outside of X3 itself which could be causing excessive load.

• Consider running the Database Engine Tuning Advisor (“mzRunDTA.cmd”)
  Refer to Microsoft documentation Using Database Engine Tuning Advisor for more information.

• Review the SQL Server logs
  Check the ERRORLOG for CHECKDB runs and when backups are being executed

• Review the Windows Performance Monitor statistics (Can use “mzSageDCScreate_README.txt” to help you set this up)
  Review the Microsoft SQL Server online help for interpretation of these counters.

• Gather SQL Server initial data for additional analysis, using the Investigation Scripts provided SQL scripts such as:
  • mzBasicSQLServerInitialData
    General overview

• mzCheckStaleStatistics
  Helps identify tables which may have stale statistics

• mzFragmentedIndexes
  Helps identify tables which may have fragmented indexes

• mzCandidates_ClusterIndexes
  Helps identify tables for which clustered indexes may be useful

• mzCandidates_LockEscalation
  Helps identify tables for which disabling lock escalation may be useful

• mzCandidates_MissingIndexes
  Helps identify tables which may have missing or unused indexes

• Gather the ongoing data over a period of time to analyse for issues, using SQL scripts such as:
  • mzBasicSQLServerMonitor
    General overview

• mzX3CurrentUserSessions
  Current X3 user sessions

• mzPSADX
  Alternative view of the current X3 user sessions

• mzDBlocks
  Lists summary of database locks

• mzX3locks
  Lists summary of X3 application level locked objects

• mzSQLServerTempDB
  Summary of tempdb usage.  Refer to Microsoft documentation TempDB Database  for more information. (In particular the "Optimizing TempDB performance in SQL Server" section)

Analysing the gathered information

To analyse this gathered information:

• Look for SQL statements that are long running or heavy I/O.
  The output from “mzBasicSQLServerMonitor” would be the starting point.

• Are there any additional indexes needed or any that are not needed and can be removed? You can review the output from the “mzCandidates_MissingIndexes.sql” script to help you identify any such indexes. Use Parameters, Usage, Data, Database Optimisation to create your own custom indexes where needed but be cautious when adding indexes as they may speed up reading data but could slow down other areas that do a lot of writing.  You should not remove or update any standard X3 provided indexes of course!   Database Optimised function is discussed in X3 specific considerations.

• Do any Statistics need to be added or updated. 
  You can review more details in the Microsoft online help Using statistics to improve query performance.

• SQL Server tuning and suggestions
  Refer to your own Microsoft SQL Server support team to ascertain the best settings for your specific circumstances.

• Do you need to cap SQL Server maximum memory
  By default, SQL Server can change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647   Unless SQL Server is running on a Windows server dedicated to SQL Server, this can potentially cause memory starvation for other processes running on the same server, such as the Sage runtime.   If you have Sage Services running on a Windows Server which is running SQL Server, it would likely be prudent to cap the amount of memory SQL Server can use.
  The following Microsoft online help links provide more information:
  How to: Set a Fixed Amount of Memory (SQL Server Management Studio)
  Effects of min and max server memory 

• Check if "Max Degree OP" is set to the default of 0
  SQL Server provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). Because SQL Server can perform a query or index operation in parallel by using several operating system threads, the operation can be completed quickly and efficiently.   The default setting of 0 would likely be the best setting, unless you have identified a specific reason to change this value.
  The following Microsoft online help links provide more information:
  How to: Configure the Number of Processors Available for Parallel Queries (SQL Server Management Studio)
  Parallel Query Processing

• SQL Server parameter "Blocked Process Threshold"
  When troubleshooting locking issues, setting the SQL Server parameter "Blocked Process Threshold" to generate reports of occurrences could be useful.  Described in Microsoft documentation.

• SQL server parameter "Cost Threshold for Parallelism"
  Described in Microsoft documentation.  On a multi-CPU system, X3 itself does not parallelize unless using 4 or 5 table joins, however Crystal Report or SEI can use parallelism.  You may be able to improve Crystal Report performance by modifying this parameter from the default value of 5.   Perform application testing with higher and lower values if needed to optimize performance.

• Data files
  User datafiles: Use dedicated filegroups for data and indexes for best performance and management.
  Log: Prebuild the log file using proper size at the start.  8GB minimum, set autogrowth appropriately, such as 1GB.

• Database options
  Leave following options as default:
  - Auto create statistics=TRUE
  - Auto Shrink=FALSE
  - Auto Update statistics=TRUE
  - Is read committed snapshot on=TRUE   Mandatory, will cause deadlocks if changed

• Cluster indexes
  Note the warning from Microsoft documentation https://docs.microsoft.com/en-us/sql/relational-databases/indexes/heaps-tables-without-clustered-indexes?view=sql-server-ver15
  WARNING: Creating or dropping clustered indexes requires rewriting the entire table. If the table has nonclustered indexes, all the nonclustered indexes must all be recreated whenever the clustered index is changed. Therefore, changing from a heap to a clustered index structure or back can take a lot of time and require disk space for reordering data in tempdb
  Sage X3 now provides clustered indexes on some tables by default.  You can review the online help for more details about clustering X3 tables and which are provided as clustered by default.
  If your analysis of the output from mzCandidates_ClusterIndexes script has concluded additional tables should be configured as clustered, then make these changes through Sage X3 via Development, Data and Parameters, Tables, Tables.  Query each table in turn, select the “Index” tab, then change the “Cluster Index” flag to “Yes” for the appropriate index.  IMPORTANT NOTE: This step will require downtime, as it requires the table to be revalidated

• Consider disabling lock escalation
  Look for any tables which have high levels of lock escalation, which may be introducing additional contention (Highlighted in output from mzCandidates_LockEscalation.sql).  For such tables you have the option to disable lock contention for the table and its indexes, which could help to reduce or eliminate this contention.
  You can read more about how lock escalation can cause blocking problems in the Microsoft online help.

• Query Store
  Consider enabling Query Store to help with future investigations.  Refer to Microsoft documentation Monitor performance by using the Query Store for more information.

General blogs, KB articles, etc.

Clustered index setting (Online help)

How to create a new index that supports the "Include" option on an X3 table? (Blog)

How to use the Database Optimization function (Blog)

How to activate a SQL Server trace from X3 (Blog)

Another Built-in SQL Server reporting tool (Blog)

SQL Server Topic - Max Degree of Parallelism (Blog)

Do you have a Deadlock problem? (Blog)

SQL Server Topic - SQL Server has to know its limitations (Blog)

Back to Index