code information for unused indexes in sql server

Hi,
I want to know about the unused indexes. Is sql server provide any direct
api or services to give information about the unused indexes.
My problem is to find indexes which are not used in certain period of time
and if yes then delete that indexes.
Please help me by providing some suggestion or solution.Perhaps you need run ITW to get some info about unused indexes
"Nikhil Kumar Jain" <Nikhil Kumar Jain@.discussions.microsoft.com> wrote in
message news:D4D1D792-5015-4D3C-B5D3-7C7474CFD95B@.microsoft.com...
> Hi,
> I want to know about the unused indexes. Is sql server provide any direct
> api or services to give information about the unused indexes.
> My problem is to find indexes which are not used in certain period of time
> and if yes then delete that indexes.
> Please help me by providing some suggestion or solution.
>|||In profiler you can get indexid for your queries for the scans event.
Run profiler for enough period of times and also run all queries which
you use for number of times.
Now from all indexid , object id in profiler and sysindex table you
can find which indexes are being used.
Remanining indexes you can decide what to do with it.
Regards
Amish|||Thanks for your reply.
Is there no other way to do the same.
Because i want to do all this things programitically. No manual intervention
is required.
I have to write a code which will search for unused indexes and delete it if
it were not used in some periods of times.
Thats why i want some alternate approach for this.
Regards,
"amish" wrote:

> In profiler you can get indexid for your queries for the scans event.
> Run profiler for enough period of times and also run all queries which
> you use for number of times.
> Now from all indexid , object id in profiler and sysindex table you
> can find which indexes are being used.
> Remanining indexes you can decide what to do with it.
> Regards
> Amish
>|||Of course, there is the Index Tuning Wizzard.
http://search.microsoft.com/search/...r />
ng+Wizard
However, it sounds like you want to do this programmatically. I don't think
that the system tables, at least in SQL Server 2000, store meta data about
the frequency of when indexes are utilized.
http://search.microsoft.com/search/...indexes

Therefore, monitoring usage of indexes would involve examining the execution
plan when a query is executed. Read up in Books Online about SET STATISTICS
PROFILE ON.
http://search.microsoft.com/search/...TICS+PROFILE+ON
When ON, a query will return it's result set followed by another result set
that contains data about the execution plan (joins, index scans, sorts,
etc.). You can write an application that parses this data (perhaps the
StmtText column) for index usage information for specific queries.
"Nikhil Kumar Jain" <NikhilKumarJain@.discussions.microsoft.com> wrote in
message news:5D59AF07-62A0-4195-951E-DC29E1A2A82A@.microsoft.com...
> Thanks for your reply.
> Is there no other way to do the same.
> Because i want to do all this things programitically. No manual
> intervention
> is required.
> I have to write a code which will search for unused indexes and delete it
> if
> it were not used in some periods of times.
> Thats why i want some alternate approach for this.
> Regards,
>
> "amish" wrote:
>