Changes to clustered indexes from 2021 R1 release onwards

3 minute read time.

Sage X3 Development team are always seeking to streamline and improve Sage X3 efficiency and performance where possible.  To this end, there are improvements in the way clustered indexes are managed introduced in 2021 R1 release, to help with performance and manageability.

Some of the discussion below will require you to understand a bit about how databases and indexing works, but hopefully all X3 administrators will find this document useful.

What are clustered indexes?

When a non-clustered index is used on an index, there is a read request to pick up the index record, followed by a second read request to pick up the data record.    A clustered index in effect combines the index and data records into one, so the database management engine only needs to do one read to find the data.   This makes finding data more efficient (one read instead of two) and also tends to allow a sort operation to be skipped, so again improving performance and reducing contention.

You can read more about SQL Server cluster indexes in the Microsoft help

What are the changes in Sage X3?

You have been able to specify your own clustered index per table for a while now, so what is actually new?   In 2021 R1 we provide some of the supervisor tables (those starting with ‘A’) predefined with a cluster index, in 2021 R2 and again in 2021 R3 the list of tables is extended further.  Review the Clustered index setting online help for the list of these tables and which release they are introduced in.

Why should I care?

In many cases you may not notice the difference, however if you are migrating or upgrading your X3 from an earlier version and have a large amount of data in any of the affected tables, it may increase the amount of time taken to upgrade.  This is because switching a table from clustered to non-clustered, or vice-versa, will require the table and indexes to be completely rebuilt.  This activity could take a significant amount of time if there is a large amount of data in the table and will also be locking the table for exclusive use whilst being converted.

You also need to be aware of the new options when you are considering setting up your own clustered index tables.

How do I check or change cluster indexes?

You can see and modify the cluster indexes by navigating to Development, Data and Parameters, Tables, Tables.  

 
Here you will see in the “Default clustered index” set, which indicates it is shipped with a cluster index.  
   
If you want to add your own clustered indexes, you can still do so.  In this case you can set “Specific clustered index” to “Yes”.   This flag is not reset by future patches, unlike the “Default clustered index” which may be changed by a patch

If you never want a clustered index on a particular table, or need to disable an existing clustered index, then check the “Clustered index deactivated” checkbox

In all cases above, you need to “Validate” the table for the change to take effect

WARNING: as previously mentioned, switching a table from clustered to non-clustered, or vice-versa, will require the table and indexes to be completely rebuilt.  This activity could take a significant amount of time if there is a large amount of data in the table and will lock the table whilst being converted.   

How can I determine which other tables may benefit from a clustered index?

This is a good question, but no easy answer as “it depends” on a lot of variables.   I am planning a future article to explore this question in more detail.

Additional reading

Sage Online Help

Tables  https://online-help.sageerpx3.com/erp/12/staticpost/tables-2/

Clustered index setting https://online-help.sageerpx3.com/erp/12/wp-static-content/public/Clustered%20index/Content/How-to%20guides/Platform/Clustered%20index/Topic%201%20Clustered%20index%20setting.htm

Microsoft documentation

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described