Tuesday, July 04, 2006

Un-used Indexes in SQL Server 2005

Question:
How to find un-used indexes in SQL Server 2005?

Gabbar:

SELECT
    o.name,
    i.name
FROM
    (
    SELECT
        x.id,
        x.indid
    FROM
        sys.dm_db_index_usage_stats us
        join sys.sysdatabases d
            on us.database_id = d.dbid
        join sys.sysindexes x
            on us.object_id = x.id and us.index_id = x.indid
        join sys.sysobjects o
            on us.object_id = o.id
    WHERE
        d.name = 'YourDatabaseName'
        AND o.xtype = 'u'
    ) used
    right outer join sys.sysindexes i
        on i.id = used.id and i.indid = used.indid
    join sysobjects o on o.id=i.id
WHERE
    o.xtype = 'u'
    and used.id is null

No comments: