UsedCount Vs OptSelectCount in monProcessObjectActivity
People uses monProcessObjectActivity table to determine unused indexes. It is a great way as non used indexes could hamper performance of your dml statements. Although, I have seen that people uses UsedCount or in fact lastUsedDate as criteria to find non used indexes. There is a issue with using UsedCount and lots of issues with using lastuseddate criteria.
First let us see what is issue with lastusedDate: People usually chekc if the index was used in last 60 days or so. I think it is wrong as it might be possible that index is quite useful for queries which run say quarterly or half yearly or yearly. Thus You shouldnt use the date criteria but instead look at UsedCount/OptSelectCount and then analyse the usage of index further and then only drop it if it is not used.
Also, sometimes people run adhoc queries on the server and that might use some index but that is something that people rarely run but now the last used date will be say yesterday and such indexes wont be in your criteria to be dropped but if you analyze them and see that it was used once or like that you might want to decide to drop it. Thus, Always analyze these factors before dropping any indexes.
Next is used count:
There are two columns in this monitoring table. UsedCount and OptSelectCount. First let us see what are these columns meaning.
1. OptSelectCount : It means that optimizer has decided to use this index for a query. But It doesnt mean that the index was in fact accessed by that query. If you will see the plancost output and you see the index name there that means the Optimizer has chosen this index.
2. UsedCount : It means that optimizer has decided to use this index for a query and it has accessed the index. You can check whether optimizer has accessed this index by looking at scancount(provided plancost mentions that this index was chosen by optimizer) if it is 0 then this index was not accessed otherwise it was. In case of nested loop join for one query optselectcount will increase by 1 but usedcount will increase by number of scans.
Now, let us see it in action.
–Create a table first
drop table mytab_1
go
create table mytab_1(col1 int identity not null ,col2 char(100) not null)
go
insert into mytab_1(col2) select top 5000 newid(1) from syscolumns
go
create nonclustered index idx_col1 on mytab_1(col1)
go
–at this point we should have 0 UsedCount and 0OptSelectCount for Indid 2
select * from master..monOpenObjectActivity where DBID = db_id() and Object_name(ObjectID,DBID) = ‘mytab_1’
go
— I am trying for 5001 even though the value doesnt exists but sybase wont know it unless it accesses the index. If it accesses the index the used count as well as optselect count will be 1.
–Now let us query the table using col1 for a value say 5000.
set statistics io,time,plancost on
go
select * from mytab_1 where col1 = 5000
go
set statistics io,time,plancost off
go
==================== Lava Operator Tree ====================
Emit
(VA = 1)
r:1 er:1
cpu: 0
/
IndexScan
idx_col1
(VA = 0)
r:1 er:1
l:3 el:3
p:0 ep:3
============================================================
Table: mytab_1 scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
select * from master..monOpenObjectActivity where DBID = db_id() and Object_name(ObjectID,DBID) = ‘mytab_1’
go
–You will see that OptSelectCount is 1 and UsedCount is 1. This is expected as Plancost shows the index was used to optselectcount was increased by 1 and then there was a index scan to get the data and thus it was used so UsedCount increased by 1.
–now rebuild index
drop index mytab_1.idx_col1
go
create nonclustered index idx_col1 on mytab_1(col1)
go
select * from master..monOpenObjectActivity where DBID = db_id() and Object_name(ObjectID,DBID) = ‘mytab_1’
go
–used count as well as optselectcount both are 0 as index is not used yet.
—now I am creating a temp table and then use that temp table to select the data from mytab_1 and this table will have 0 rows.
select 5000 as col1 into #mytab
go
delete from #mytab
go
select * from #mytab
go
select * from master..monOpenObjectActivity where DBID = db_id() and Object_name(ObjectID,DBID) = ‘mytab_1’
go
—You will see optselectcount and UsedCount to be 0
set statistics io,time,plancost off
go
select * from #mytab mt inner join mytab_1 mt1 on mt.col1 = mt1.col1
go
set statistics io,time,plancost off
go
–output of the statistics io,time, plancost
==================== Lava Operator Tree ====================
Emit
(VA = 3)
r:0 er:1
cpu: 0
/
NestLoopJoin
Inner Join
(VA = 2)
r:0 er:1
/ \
TableScan IndexScan
#mytab (mt) idx_col1 (mt1)
(VA = 0) (VA = 1)
r:0 er:1 r:0 er:1
l:1 el:1 l:0 el:3
p:0 ep:1 p:0 ep:3
============================================================
Table: #mytab (mt) scan count 1, logical reads: (regular=1 apf=0 total=1), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: mytab_1 (mt1) scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0
select * from master..monOpenObjectActivity where DBID = db_id() and Object_name(ObjectID,DBID) = ‘mytab_1’
go
You will see OptSelectCount to be 1 but usedCount to be 0. Now plancost shows that index was used and thus optselectcount was increased by 1. Look at scan count and it is 0 and that means that index was not accessed physically and thus usedcount stayed 0.
If say you will get 0 rows for say one month in your table based on your logic then usedcount will be 0 but optselectcount will keep on increasing. Note that lastuseddate will be null as index is not used. Now if you drop this index as it has not been used in last 60 days or based on that usedcount is 0.
Then a day comes when your temporary table in fact has some data and you do not have required index. It will use a reformatting strategy or uses some other sort of very expensive plan.
Conclusion: Do not drop indexes based on usedcount and lastusedcount column but take mainly optselectcount into account. Also, before dropping indexes make sure that after server was restarted all your load has been executed once atleast.
In case you decide to drop index try to keep the statistics on leading column of dropped index as it might be helpful in estimating rows where we have that column in sarg alongwith sargs on other columns.
Thanks