How To Find Least And Most Used Index(es) In A Table

Its very easy to find unused, used and missing index details from DMVs in SQL Server however its not so easy in Sybase ASE. We tend to create so many indexes on a table and then suddenly application team starts complaining that application has become very slow. Newly introduced index starts affecting the write operation more than its doing to select operation. As you already know – Index helps in select operation whereas it slows down insert/update and delete operation.

In order to understand the situation in a better way, we need to have underlying number to see whether newly introduced index is helping or hindering. If number of write operations are way higher than number of read operation then, its safer to drop the index after due diligence however if its other way around then, we shall need to look at other possible aspects of query.

Below query would help in getting numbers which could help in deciding about potential of index and whether to continue with it or declare it as a candidate to be dropped:

select
“Table” = object_name(ObjectID, DBID),
IndID = IndexID, si.name, LogicalReads, RowsInserted,
RowsDeleted,
RowsUpdated, OptSelectCount,
UsedCount, LastOptSelectDate
from master..monOpenObjectActivity oa, sysindexes si
where oa.ObjectID = si.id
and oa.ObjectID = object_id(‘Table_Name’) — Object id of table which you want to check
and oa.IndexID = si.indid
and ObjectID > 99
and IndexID > 1 and IndexID != 255
and DBID = db_id() AND si.status2 & 512 = 0
order by 1,2

Sample output will be as follows:

Table	IndID	name	LogicalReads	RowsInserted	RowsDeleted	RowsUpDATEFIELDd	OptSelectCount	UsedCount	LastOptSelectDate
Table_Name	3	Table_Name_IDX1	48190856	9494404	0	0	4741	509453	Mar 27 2021 11:13AM
Table_Name	4	Table_Name_IDX2	82822120	9494404	0	0	2954	10015128	Mar 27 2021  9:38AM
Table_Name	5	IDX_ARCH	29812297	9494404	0	0	444	11548	Mar 27 2021  9:38AM
Table_Name	6	Table_Name_IDX_FC	36591366	7945549	0	0	1266	643456	Mar 27 2021 11:47AM
Table_Name	7	IDX_Table_NameAVAIL	25407918	7945932	383	0	7979	23949	Mar 27 2021 12:21PM
Table_Name	8	Table_Name_IDX4	27000942	7961030	15481	0	319	3224	Mar 26 2021  9:44PM
Table_Name	9	Table_Name_IDX_SETNT	50720970	7945932	383	0	1559	18529	Mar 27 2021  9:12AM
Table_Name	10	IDX_Table_Name_TMP_V	25937869	7945549	0	0	321	2379	Mar 25 2021  2:14PM
Table_Name	11	IDX_Table_Name_TEMP	28726784	7945549	0	0	79	6175	Mar 18 2021  9:18AM
Table_Name	12	Table_Name_RIPS2	42788575	7975091	29542	0	189	729	Mar 26 2021 11:02PM
Table_Name	13	IDX_Table_Name_CTS	36976787	7945549	0	0	723	86494	Mar 27 2021 12:15PM
Table_Name	14	Table_Name_IDX9	43497553	7945549	0	0	21548	2193779	Mar 27 2021 12:05PM
Table_Name	15	Table_Name_IDX6	329530575	7945549	0	0	5684	1173639	Mar 27 2021 11:48AM
Table_Name	16	IDX_Table_Name_INTER_BR	34262792	7945549	0	0	4993	20238	Mar 27 2021 10:32AM
Table_Name	17	IDX_Table_Name_POST	24813809	7974856	29307	0	203	203	Mar 26 2021 11:19PM
Table_Name	18	Table_Name_IDX10	25733736	7945549	0	0	320	9781	Mar 27 2021 10:27AM
Table_Name	19	IDX_Table_Name_ADD	16270758	5054955	0	0	121	160	Mar 27 2021  6:16AM
Table_Name	20	IDX_Table_Name_BR_CODE	11207957	3130986	0	0	131	610	Mar 27 2021  7:51AM

As you may see here, logical reads are way higher than all the write operations combined together. So, defined indexes are helping rather hindering.

Please note that, in order to use this query MDA table should be enabled. You may read more about MDA tables here.

Please leave your comment if you liked this post or have any feedback.