How to tell if an Index has ever been used...

8 Sep 2006 7:31 PM

Rob

Hi,

Is there a way to tell if an index has EVER been used on a table (apart from
going back in time and setting up a trace for it)?

Thanks.

8 Sep 2006 7:38 PM

Tracy McKibben

Rob wrote:

> Hi,
>
> Is there a way to tell if an index has EVER been used on a table (apart from
> going back in time and setting up a trace for it)?
>
> Thanks.

Which version of SQL?

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

8 Sep 2006 8:06 PM

Rob

Sorry, for SQL 2000

Show quote

"Tracy McKibben" wrote:

> Rob wrote:
> > Hi,
> >
> > Is there a way to tell if an index has EVER been used on a table (apart from
> > going back in time and setting up a trace for it)?
> >
> > Thanks.
>
> Which version of SQL?
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

8 Sep 2006 8:10 PM

Tracy McKibben

Rob wrote:

> Sorry, for SQL 2000
>

Unfortunately, Profiler is your only true option... Sounds like you
already knew that though...

http://www.sql-server-performance.com/lm_index_elimination_english.asp

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

8 Sep 2006 7:42 PM

Aaron Bertrand [SQL Server MVP]

Depends on what version of SQL Server you are using.

In SQL Server 2005, you can get index usage stats from here:

SELECT
    TableName = OBJECT_NAME(s.[object_id]),
    IndexName = i.name,
    s.last_user_seek,
    s.last_user_scan,
    s.last_user_lookup,
    s.last_user_update,
    s.last_system_seek,
    s.last_system_scan,
    s.last_system_lookup,
    s.last_system_update
FROM
    sys.dm_db_index_usage_stats s
INNER JOIN
    sys.indexes i
ON
    s.[object_id] = i.[object_id]
    AND s.index_id = i.index_id
WHERE
    s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0;

Of course if you are interested in a specific table, or table + index, you
could add to the where clause accordingly.

A

Show quote

"Rob" <R**@discussions.microsoft.com> wrote in message
news:CD88E3F9-C22A-4B3C-B45A-7BB44DC772F0@microsoft.com...
> Hi,
>
> Is there a way to tell if an index has EVER been used on a table (apart
> from
> going back in time and setting up a trace for it)?
>
> Thanks.

12 Sep 2006 8:56 AM

Uri Dimant

Rob
All indexes which haven’t been used yet can be retrieved with the following
statement:

select object_name(object_id), i.name
from sys.indexes i
where 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 object_name(object_id) asc

Show quote

"Rob" <R**@discussions.microsoft.com> wrote in message
news:CD88E3F9-C22A-4B3C-B45A-7BB44DC772F0@microsoft.com...
> Hi,
>
> Is there a way to tell if an index has EVER been used on a table (apart
> from
> going back in time and setting up a trace for it)?
>
> Thanks.

12 Sep 2006 10:05 AM

Tibor Karaszi

.... and remember that this is since last restart of the database engine.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Uri Dimant" <u***@iscar.co.il> wrote in message news:u1QMzkk1GHA.3476@TK2MSFTNGP04.phx.gbl...
> Rob
> All indexes which haven't been used yet can be retrieved with the following statement:
>
> select object_name(object_id), i.name
> from sys.indexes i
> where 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 object_name(object_id) asc
>
>
>
> "Rob" <R**@discussions.microsoft.com> wrote in message
> news:CD88E3F9-C22A-4B3C-B45A-7BB44DC772F0@microsoft.com...
>> Hi,
>>
>> Is there a way to tell if an index has EVER been used on a table (apart from
>> going back in time and setting up a trace for it)?
>>
>> Thanks.
>
>