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('expenses')
order by UsedCount
This exmaple displays all indexes that are not currently used in an application or server:
select DBID , ObjectID, IndexID , object_name(ObjectID, DBID)
from monOpenObjectActivity
where DBID = db_id("financials_db") and OptSelectCount = 0
This example displays all indexes that are not currently used in an application, and also provides a sample output:
select DBID , ObjectID, IndexID , object_name(ObjectID, DBID)
from monOpenObjectActivity
where DBID = db_id("financials_db") and OptSelectCount = 0
ObjectName id IndexName OptCtLast OptSelectDate UsedCount LastUsedDate ---------- --- --------------------- ------------ ----------------- ----- -------------------------- customer 2 ci_nkey_ckey 3 Sep 27 2002 4:05PM 20 Sep 27 2002 4:05PM customer 0 customer_x 3 Sep 27 2002 4:08PM 5 Sep 27 2002 4:08PM customer 1 customer_x 1 Sep 27 2002 4:06PM 5 Sep 27 2002 4:07PM customer 3 ci_ckey_nkey 1 Sep 27 2002 4:04PM 5 Sep 27 2002 4:05PM customer 4 customer_nation 0 Jan 1 1900 12:00AM 0 Jan 1 1900 12:00AM
In this example, the customer_nation index has not been used, which results in the date “Jan 1 1900 12:00AM”.
