SAP ASE 16.04 - Indexes: How to check the use of second columns in multiple columns indexes

[davidpc]

------------------------------------------ ENGLISH -----------------------------------------------------------------

I have SAP ASE 16.04 database.

Is there any query that shows the use of the columns of indexes? I have several indexes with multiple columns keys and I want to check if they are efficient enough. With the classic monitoring table (master..monOpenObjectActivity) I only see if the index is used:

select *
from master..monOpenObjectActivity
where DBID = db_id() and IndexID >= 1
and object_name(ObjectID) = "TABLA"

It is interesting for me to know that, in order to avoid those unnecessary key columns if they are not used.

Thanks in advance.

------------------------------------------ SPANISH -----------------------------------------------------------------

Actualmente empleo SAP ASE 16.04. Estaría interesado en saber si hay alguna manera de ver si en una consulta se está haciendo uso de las columnas de un índice que tiene múltiples columnas clave. Estoy utilizando las tablas de monitorización pero no veo que me faciliten esa información.

En mi caso, si supiera que solo se utilizan las claves primarias de los índices me plantaría eliminar las columnas clave adicionales.

Gracias de antemano.

[former_member188958]

I'm not coming up with anything direct. A possible approach may be to create another index that has just the first column. The optimizer will likely choose it (being smaller) when the second column isn't needed. You could then compare the number of times each index was used to determine how often the second column was considered useful.

[davidpc]

Ok, thanks for the answer. Most probably I'm wrong, but I have the feeling that in that way it's not accurate enough. Previously I tried doing this, creating several indexes with different number of columns (my doubt is between 2 or 3 columns index). Depending on the p. memory, the objects in memory and the query plan optimizer I deduced that sometimes it doesn't use the best multi-column index. According to the Query Process Plan, forcing manually the multi-column index it worked slightly better with the 3 columns index. Assuming that there is no other option, perhaps I'll test it again just in case.

Again, thanks.