Unused Indexes in your databases

  • Comments posted to this topic are about the item Unused Indexes in your databases

  • There are two ORDER BY clauses in a row. The script does not work.

  • Also two where clauses. Fixed the two duplicates, but still have a page of error messages.

  • Double Order By and Where clauses. Even if I comment one of the doubles out for each, the script still fails with errors:

    Msg 207, Level 16, State 1, Line 34

    Invalid column name 'user_scans'.

    Msg 207, Level 16, State 1, Line 34

    Invalid column name 'user_lookups'.

    Msg 4104, Level 16, State 1, Line 34

    The multi-part identifier "s.object_id" could not be bound.



    Pat Buskey

  • select object_name (i.object_id) as NomTable,isnull( i.name,'HEAP') as IndexName

    from sys.objects o inner join sys.indexes i

    ON i.[object_id] = o.[object_id] left join

    sys.dm_db_index_usage_stats s

    on i.index_id = s.index_id and s.object_id = i.object_id

    where object_name (o.object_id) is not null

    and object_name (s.object_id)

    is null

    AND o.[type] = 'U'

    and isnull( i.name,'HEAP') <>'HEAP'

    union all

    /*

    part 2 : indexes in sys.dm_db_index_usage_stats with

    •user_seeks= 0

    •user_scans=0

    •user_lookups= 0

    */

    select object_name (i.object_id) as NomTable,isnull( i.name,'HEAP') as IndexName

    from sys.objects o inner join sys.indexes i

    ON i.[object_id] = o.[object_id] left join

    sys.dm_db_index_usage_stats s

    on i.index_id = s.index_id and s.object_id = i.object_id

    where user_seeks= 0

    and user_scans=0

    and user_lookups= 0

    AND o.[type] = 'U'

    and isnull( i.name,'HEAP') <>'HEAP'

    order by NomTable asc

  • I have a question.

    Does this statistics persist after a SQL Server service restart? or server reboot etc.

  • There are several errors in the code as presented. Please clean your code before you publish it.

  • i'am sorry, it is just one error of copy/past the good script is:

    /*

    Author: rabie harriga

    Version:SQL 2005, 2008, 2008 R2

    Decription:This Script allows you to determine the list of unused indexes in your databases

    */

    select object_name (i.object_id) as NomTable,isnull( i.name,'HEAP') as IndexName

    from sys.objects o inner join sys.indexes i

    ON i.[object_id] = o.[object_id] left join

    sys.dm_db_index_usage_stats s

    on i.index_id = s.index_id and s.object_id = i.object_id

    where object_name (o.object_id) is not null

    and object_name (s.object_id)

    is null

    AND o.[type] = 'U'

    and isnull( i.name,'HEAP') <>'HEAP'

    union all

    select object_name (i.object_id) as NomTable,isnull( i.name,'HEAP') as IndexName

    from sys.objects o inner join sys.indexes i

    ON i.[object_id] = o.[object_id] left join

    sys.dm_db_index_usage_stats s

    on i.index_id = s.index_id and s.object_id = i.object_id

    where user_seeks= 0

    and user_scans=0

    and user_lookups= 0

    AND o.[type] = 'U'

    and isnull( i.name,'HEAP') <>'HEAP'

    order by NomTable asc

  • The revised script works, but returns lots of duplicates, and also includes primary/unique key indexes (it doesn't matter if an index is 'used' if it's there to enforce integrity).

  • U can remove identity and unique keys from select by adding

    and i.is_primary_key = 0 and i.is_unique = 0

    to both where clauses

  • It is also helpful to know how much space is used by the index. Here is how I did that:

    CONVERT(DECIMAL(8,2), i.dpages / 128.0) AS MBUsed

    EDIT: I should note that I used sys.sysindexes rather than sys.indexes. However, sys.sysindexes may not be supported in future versions of SQL Server.

  • I am getting an error when I add that in (invalid column name dpages). Can you post the complete SQL?

  • @Randall: Understand that this is using the sys.sysindexes view that may not be supported in future versions.

    SELECTLEFT(OBJECT_NAME (i.id), 50)AS TableName,

    LEFT(ISNULL(i.name,'HEAP'), 50)AS IndexName,

    CASE WHEN u.object_id IS NULL THEN 'No Data' ELSE 'No Usage' END AS Determination,

    i.dpagesAS PagesUsed,

    CONVERT(DECIMAL(8,2), i.dpages / 128.0) AS MBUsed

    FROMsys.objects o

    JOINsys.sysindexes i

    ONi.id = o.object_id

    LEFT JOIN sys.dm_db_index_usage_stats u

    ONi.indid = u.index_id

    ANDu.object_id = i.id

    WHEREo.type = 'U'-- Exclude system tables.

    ANDi.name IS NOT NULL-- Exclude heaps

    ANDi.name NOT LIKE '_WA_Sys%'-- Exclude statistics

    AND(u.object_id IS NULL-- Either no usage stats, or...

    OR(u.user_seeks = 0-- all usage stats are zero.

    ANDu.user_scans = 0

    ANDu.user_lookups = 0

    )

    )

    ORDER BY i.dpages DESC

  • Thank you. That took care of my problem.

  • Why used Union all, if run query after Union all separately it gives 0 rows in result. However only some additional clauses are in where clause of query after Union all. Cannot it be done in a single query without using Union all. Please explain

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply