Index monitoring

[03Explorer]

Is there any way to monitor if an Index is being used, let's say for a month's worth of watching? We have a SQL Server 2005 with a database TABLE with sizes:

Total data size 965,756.672 MB
Total Index size 1,726,825.664 MB
(# of Indexes is 6)

Something is out of control. My job description is a report analyst with limited Database Admin Exposure. I would like to ask our DBAs to research ... {something} to see if we can bring the Indexes into control. Currently we have 6 indexex on the table and they were implimented by former team members prior to anyone on the current team. Our DBAs are NOT intimate with our structure enough to guide us other than complain that it is or appears too large (the index that is).

 

[JayKusch]

Here is a script I have had for a while (did not write it but I use it).

DECLARE  @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT   OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
            INDEXNAME = I.NAME,
            I.INDEX_ID
    FROM     SYS.INDEXES I
            JOIN SYS.OBJECTS O
            ON I.OBJECT_ID = O.OBJECT_ID
    WHERE    OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
        AND I.INDEX_ID NOT IN (
    SELECT S.INDEX_ID
        FROM   SYS.DM_DB_INDEX_USAGE_STATS S
        WHERE  S.OBJECT_ID = I.OBJECT_ID
            AND I.INDEX_ID = S.INDEX_ID
            AND DATABASE_ID = @dbid)
    ORDER BY OBJECTNAME,
            I.INDEX_ID,
            INDEXNAME ASC

Thanks

J. Kusch

 

[ZenRaven]

You could use Profiler to capture anything ran against the specified table for whatever time period you choose. You could then use the file produced by Profiler in the Database Engine Tuning Advisor. I believe the DTA results have a report on usage statistics by object.