Indexes

Hello,
Anyone knows how to determine if an index is being used.
For example: I have several tables which have multiple indexes.
I want to know which of them are not being used so I can delete them and
release some space.
Is there a stored procedure to get these statistics?
TIA
Regards,
Eduardo SicouretWhat version of SQL Server? SQL Server 2000 doesn't keep track of such information, so you'd have to
do it using a Profiler trace or similar. 2005 does, and you can look at the information using some
of the new dynamic management views (dm_db_missing_indexes_%).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
> Hello,
> Anyone knows how to determine if an index is being used.
> For example: I have several tables which have multiple indexes.
> I want to know which of them are not being used so I can delete them and release some space.
> Is there a stored procedure to get these statistics?
> TIA
> Regards,
> Eduardo Sicouret
>|||I'm sorry...
I'm using SQL Server 2000...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribió
en el mensaje news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server? SQL Server 2000 doesn't keep track of such
> information, so you'd have to do it using a Profiler trace or similar.
> 2005 does, and you can look at the information using some of the new
> dynamic management views (dm_db_missing_indexes_%).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> Anyone knows how to determine if an index is being used.
>> For example: I have several tables which have multiple indexes.
>> I want to know which of them are not being used so I can delete them and
>> release some space.
>> Is there a stored procedure to get these statistics?
>> TIA
>> Regards,
>> Eduardo Sicouret
>|||You are in for a lot more work, then and the question is whether you want to do this in the end. You
can use Profiler to catch the execution plans, save such a trace and parse the text for the
execution plans to see what indexes were used, compare that to the indexes in the database and see
which weren't used.
Also, I by mistake types the wrong name for the dynamic management views for 2005, it should be
sys.dm_db_index_usage_stats and possibly
dm_db_index_operational_stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> I'm sorry...
> I'm using SQL Server 2000...
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribió en el mensaje
> news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
>> What version of SQL Server? SQL Server 2000 doesn't keep track of such information, so you'd have
>> to do it using a Profiler trace or similar. 2005 does, and you can look at the information using
>> some of the new dynamic management views (dm_db_missing_indexes_%).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Eduardo Sicouret" <esicouret> wrote in message news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> Anyone knows how to determine if an index is being used.
>> For example: I have several tables which have multiple indexes.
>> I want to know which of them are not being used so I can delete them and release some space.
>> Is there a stored procedure to get these statistics?
>> TIA
>> Regards,
>> Eduardo Sicouret
>>
>|||I'm not sure what causes updates to that usage table. I've tested it by
doing queries against my tables, inspecting the execution plan and then
finding no usage entry for the index indicated by the execution plan...what
gives?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uan97RpfGHA.324@.TK2MSFTNGP02.phx.gbl...
> You are in for a lot more work, then and the question is whether you want
> to do this in the end. You can use Profiler to catch the execution plans,
> save such a trace and parse the text for the execution plans to see what
> indexes were used, compare that to the indexes in the database and see
> which weren't used.
> Also, I by mistake types the wrong name for the dynamic management views
> for 2005, it should be
> sys.dm_db_index_usage_stats and possibly
> dm_db_index_operational_stats.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl...
>> I'm sorry...
>> I'm using SQL Server 2000...
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> escribió en el mensaje news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
>> What version of SQL Server? SQL Server 2000 doesn't keep track of such
>> information, so you'd have to do it using a Profiler trace or similar.
>> 2005 does, and you can look at the information using some of the new
>> dynamic management views (dm_db_missing_indexes_%).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> Anyone knows how to determine if an index is being used.
>> For example: I have several tables which have multiple indexes.
>> I want to know which of them are not being used so I can delete them
>> and release some space.
>> Is there a stored procedure to get these statistics?
>> TIA
>> Regards,
>> Eduardo Sicouret
>>
>>
>