Examples of using index selection

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