The following example queries the monitoring tables for the last time all indexes for a specific object were selected by the optimizer, as well as the last time they were actually used during execution, and reports the counts in each case:
select DBID, ObjectID, IndexID, OptSelectCount, LastOptSelectDate, UsedCount, LastUsedDate
from monOpenObjectActivity
where DBID=db_id("financials_db") and ObjectID = object_id('financials_db..expenses')
order by UsedCount
This example displays all indexes that are used—or not currently used—in an application:
select DBID, ObjectID, IndexID, ObjectName = object_name(ObjectID, DBID),
LastOptSelectDate, UsedCount, LastUsedDate
from monOpenObjectActivity
where DBID = db_id("MY_1253_RS_RSSD")
and ObjectID = object_id('MY_1253_RS_RSSD..rs_columns')
DBID ObjectID IndexID ObjectName LastOptSelectDate UsedCount LastUsedDate ---------- ---------- ----------- ------------------------------ ----------------------- ----------- -------------------------- 4 192000684 0 rs_columns May 15 2006 4:18PM 450 May 15 2006 4:18PM 4 192000684 1 rs_columns NULL 0 NULL 4 192000684 2 rs_columns NULL 0 NULL 4 192000684 3 rs_columns May 12 2006 6:11PM 1 May 12 2006 6:11PM 4 192000684 4 rs_columns NULL 0 NULL 4 192000684 5 rs_columns NULL 0 NULL
If the index is not used, it results in a NULL date. If an index is used, it results in a date like “May 15 2006 4:18PM.”
In this example, the query displays all indexes that are not currently used in the current database:
select DB = convert(char(20), db_name()), TableName = convert(char(20), object_name(i.id, db_id())), IndexName = convert(char(20),i.name), IndID = i.indid from master..monOpenObjectActivity a, sysindexes i where a.ObjectID =* i.id and a.IndexID =* i.indid and (a.UsedCount = 0 or a.UsedCount is NULL) and i.indid > 0 and object_name(i.id, db_id()) not like "sys%" order by 2, 4 asc
DB TableName IndexName IndID ------------------- -------------------- -------------------- ------ MY_1253_RS_RSSD rs_articles rs_key_articles 1 MY_1253_RS_RSSD rs_articles rs_key4_articles 2 MY_1253_RS_RSSD rs_classes rs_key_classes 1 MY_1253_RS_RSSD rs_classes rs_key2_classes 2 MY_1253_RS_RSSD rs_config rs_key_config 1 MY_1253_RS_RSSD rs_databases rs_key_databases 1 MY_1253_RS_RSSD rs_databases rs_key9_databases 2 MY_1253_RS_RSSD rs_databases rs_key13_databases 3 MY_1253_RS_RSSD rs_databases rs_key14_databases 4 MY_1253_RS_RSSD rs_databases rs_key15_databases 5 MY_1253_RS_RSSD rs_datatype rs_key_datatypes 1 MY_1253_RS_RSSD rs_datatype rs_key2_datatype 2
