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

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:

“Table” = object_name(ObjectID, DBID),
IndID = IndexID,, LogicalReads, RowsInserted,
RowsUpdated, OptSelectCount,
UsedCount, LastOptSelectDate
from master..monOpenObjectActivity oa, sysindexes si
where oa.ObjectID =
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_Name3Table_Name_IDX1481908569494404004741509453Mar 27 2021 11:13AM
Table_Name4Table_Name_IDX282822120949440400295410015128Mar 27 2021  9:38AM
Table_Name5IDX_ARCH2981229794944040044411548Mar 27 2021  9:38AM
Table_Name6Table_Name_IDX_FC365913667945549001266643456Mar 27 2021 11:47AM
Table_Name7IDX_Table_NameAVAIL2540791879459323830797923949Mar 27 2021 12:21PM
Table_Name8Table_Name_IDX42700094279610301548103193224Mar 26 2021  9:44PM
Table_Name9Table_Name_IDX_SETNT5072097079459323830155918529Mar 27 2021  9:12AM
Table_Name10IDX_Table_Name_TMP_V259378697945549003212379Mar 25 2021  2:14PM
Table_Name11IDX_Table_Name_TEMP28726784794554900796175Mar 18 2021  9:18AM
Table_Name12Table_Name_RIPS2427885757975091295420189729Mar 26 2021 11:02PM
Table_Name13IDX_Table_Name_CTS3697678779455490072386494Mar 27 2021 12:15PM
Table_Name14Table_Name_IDX943497553794554900215482193779Mar 27 2021 12:05PM
Table_Name15Table_Name_IDX632953057579455490056841173639Mar 27 2021 11:48AM
Table_Name16IDX_Table_Name_INTER_BR34262792794554900499320238Mar 27 2021 10:32AM
Table_Name17IDX_Table_Name_POST248138097974856293070203203Mar 26 2021 11:19PM
Table_Name18Table_Name_IDX10257337367945549003209781Mar 27 2021 10:27AM
Table_Name19IDX_Table_Name_ADD16270758505495500121160Mar 27 2021  6:16AM
Table_Name20IDX_Table_Name_BR_CODE11207957313098600131610Mar 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.

Leave a Comment

Your email address will not be published. Required fields are marked *