• Thank you for the reply,

    Ok let me kind of get this right,

    Used the index usage dmv to figure out what indexes are not used very often.

    found few indexes which run very infrequently used but they are seeks,

    The amount of seeks will not help becuse the query may run once a week or a end of day process.

    so i wanted to know if drop an index which i have identified by the query stats. then what queries will be affected when i drop this perticular index.

    just to make sure the infrequent queries dont impact the SLA's which are setup for the EOD or EOM processes.

    To Re-iterate

    the steps i followed

    1) use Sys.DM_DB_INDEX_USAGE_STATS

    SELECT

    OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),

    INDEXNAME = I.NAME,

    I.INDEX_ID,

    S.*

    FROM

    SYS.INDEXES I

    INNER JOIN SYS.OBJECTS O

    ON I.OBJECT_ID = O.OBJECT_ID

    INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S

    ONS.OBJECT_ID = I.OBJECT_ID

    AND I.INDEX_ID = S.INDEX_ID

    AND DATABASE_ID = @dbid

    WHERE

    OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1

    AND I.NAME IS NOT NULL and

    OBJECT_NAME(I.OBJECT_ID) not like 'sys%'

    AND I.NAME not like 'PK%' -- Ignore Pk's

    and I.NAME not like 'UC%' -- Ignore unique constraints.

    ORDER BY (s.user_seeks+s.user_scans+s.user_lookups+s.user_updates) asc

    2) Use the Indexes which are not frequently used. Do an impact analysys.

    select object_name(objectid),

    query_plan.query('count[//@Index=''[IDX_SettledMonthToDate]'']') as result,

    query_plan

    from sys.dm_exec_cached_plans pl

    cross apply sys.dm_exec_query_plan(pl.plan_handle)

    where dbid = 14

    This code is not returning all the query plans used by sql server. and unable to see what processes/procedures/AdhocSql are using this perticular index

    Could you please help me with a query so that i can isolate the sql text which is going to be using this perticular index or may be the procedure name which will be impacted.

    This is what i want to know. plese help.

    Regards
    Vinay