When you're occurring performance issue, you should first seek for missing SQL indexes. Indeed, when SQL index is missing, your database needs to run a full scan on the table (more CPU consumption, and less CPU for the other queries) and SQL query response is slowing down by this heavy processing.

Check SQL index existence

In HAC, you can easily check SQL index existence for each table: /hac/azurehac/schema/

hac/azurehac/schema/tables/

Find missing SQL index

In HAC, you can select time window where performance issue occurred for retrieving heaviest SQL queries. Then, you can display Query Plan where missing SQL indexes could be identified.

/hac/azurehac/query/

It can happen that longest query log is not available: “Query Store is not properly configured”. In this case, you should create SAP support ticket to re-activate it.

ALTER DATABASE CURRENT SET QUERY_STORE CLEAR;

From items.xml (recommended)

When it is possible, you should create SQL index in items.xml. There's several advantages for:

• Index will be created at each migrate data deployment. You don't have to manually create this missing index for each env.
• Index won't be overwritten or removed during migrate data deployment.

However, items.xml doesn't allow you to create index in table relation. For this specific case, you need to create manually the index (see next chapter).

SQL index creation example with inclusion

CREATE NONCLUSTERED INDEX [] 
ON [dbo].[products] ([TypePkString],[p_catalogversion]
INCLUDE ([p_baseproduct])

Note that attribute TypePkString is named itemtype in items.xml

From HAC

When you need to create manually SQL index from HAC, you must name it by using following pattern defined in Managed properties. Otherwise, your index will be dropped on the next migrate data deployment.

bootstrap.init.type.system.custom.index.ignore.names.starting.with=nci_wi_, idx_*dba, nci_msft_

There's two case which is justified to create manually SQL index :

• For the relation table
• In runtime emergency situation as the live production system

In HAC, it is possible to use Groovy for this index creation. Pay attention, index creation, in large table, can generate heavy workload on database system. It is recommended to execute them only during low traffic period.

https://me.sap.com/notes/0003508357

import de.hybris.platform.core.Registry

conn = Registry.getCurrentTenant().getDataSource().getConnection()
stmt = conn.createStatement()
ddlQuery = "YOUR CREATE INDEX SQL COMMAND"
stmt.executeUpdate(ddlQuery)

From SAP Support

Finally, you can also ask DBA support for this SQL index creation by creation SAP support ticket in https://me.sap.com/app/casecreate

As we can see, identify missing SQL indexes and to create them is easy task. Nevertheless, you should keep in mind that SQL index creation can lead to few performance degradation in write access and it will increase your database storage usage. 

In term of cost, database CPU usage is larger more expensive than database storage. And eCommerce solution is mostly read access system for critical business operation (frontend activity). By consequence, when SQL index is missing and it impacts your performance, you should probably need to create it.

Table size is another aspect that should be considered. Over 1 million entry, SQL index starts to become less efficient. And you should do some data cleaning operation and/or redesign your data model by introduction deployment table (additional technical table behind logical SAP Commerce Cloud table).

If you've doubt or if you're encountering some difficulty, you should not hesitate to contact direct SAP Expert Service :  sapcx-expertservices@sap.com