SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Rabie Harriga / Unused Indexes in your databases / Latest Posts

RE: Unused Indexes in your databases

swatmadan.siet — Sat, 30 Mar 2013 06:25:16 GMT

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

RE: Unused Indexes in your databases

randal.schmidt — Tue, 23 Aug 2011 09:23:13 GMT

Thank you. That took care of my problem.

RE: Unused Indexes in your databases

fahey.jonathan — Tue, 23 Aug 2011 09:19:59 GMT

@Randall: Understand that this is using the sys.sysindexes view that may not be supported in future versions.[code="sql"]SELECT LEFT(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.dpages AS PagesUsed, CONVERT(DECIMAL(8,2), i.dpages / 128.0) AS MBUsedFROM sys.objects o JOIN sys.sysindexes i ON i.id = o.object_id LEFT JOIN sys.dm_db_index_usage_stats u ON i.indid = u.index_id AND u.object_id = i.idWHERE o.type = 'U' -- Exclude system tables. AND i.name IS NOT NULL -- Exclude heaps AND i.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. AND u.user_scans = 0 AND u.user_lookups = 0 ) )ORDER BY i.dpages DESC[/code]

RE: Unused Indexes in your databases

randal.schmidt — Tue, 23 Aug 2011 08:57:06 GMT

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

RE: Unused Indexes in your databases

fahey.jonathan — Tue, 23 Aug 2011 08:41:55 GMT

It is also helpful to know how much space is used by the index. Here is how I did that:[code="sql"]CONVERT(DECIMAL(8,2), i.dpages / 128.0) AS MBUsed[/code]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.

RE: Unused Indexes in your databases

Jaroslav Pfauser — Thu, 17 Mar 2011 06:42:24 GMT

U can remove identity and unique keys from select by addingand i.is_primary_key = 0 and i.is_unique = 0 to both where clauses

RE: Unused Indexes in your databases

Toreador — Thu, 17 Mar 2011 04:35:03 GMT

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).

RE: Unused Indexes in your databases

harriga.rabie-1008938 — Thu, 24 Feb 2011 05:58:37 GMT

i'am sorry, it is just one error of copy/past the good script is:/*Author: rabie harrigaVersion:SQL 2005, 2008, 2008 R2Decription: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 iON i.[object_id] = o.[object_id] left joinsys.dm_db_index_usage_stats s on i.index_id = s.index_id and s.object_id = i.object_idwhere object_name (o.object_id) is not nulland object_name (s.object_id) is nullAND o.[type] = 'U'and isnull( i.name,'HEAP') <>'HEAP'union allselect object_name (i.object_id) as NomTable,isnull( i.name,'HEAP') as IndexName from sys.objects o inner join sys.indexes iON i.[object_id] = o.[object_id] left joinsys.dm_db_index_usage_stats s on i.index_id = s.index_id and s.object_id = i.object_idwhere user_seeks= 0and user_scans=0and user_lookups= 0AND o.[type] = 'U'and isnull( i.name,'HEAP') <>'HEAP'order by NomTable asc

RE: Unused Indexes in your databases

Dave Vroman — Wed, 23 Feb 2011 13:41:54 GMT

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

RE: Unused Indexes in your databases

mgigani-696113 — Wed, 23 Feb 2011 09:08:31 GMT

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

RE: Unused Indexes in your databases

harriga.rabie-1008938 — Wed, 23 Feb 2011 08:28:33 GMT

select object_name (i.object_id) as NomTable,isnull( i.name,'HEAP') as IndexName from sys.objects o inner join sys.indexes iON i.[object_id] = o.[object_id] left joinsys.dm_db_index_usage_stats s on i.index_id = s.index_id and s.object_id = i.object_idwhere object_name (o.object_id) is not nulland object_name (s.object_id) is nullAND 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 iON i.[object_id] = o.[object_id] left joinsys.dm_db_index_usage_stats s on i.index_id = s.index_id and s.object_id = i.object_idwhere user_seeks= 0and user_scans=0and user_lookups= 0AND o.[type] = 'U'and isnull( i.name,'HEAP') <>'HEAP'order by NomTable asc

RE: Unused Indexes in your databases

bustell — Wed, 23 Feb 2011 08:02:49 GMT

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 34Invalid column name 'user_scans'.Msg 207, Level 16, State 1, Line 34Invalid column name 'user_lookups'.Msg 4104, Level 16, State 1, Line 34The multi-part identifier "s.object_id" could not be bound.

RE: Unused Indexes in your databases

gitmo — Wed, 23 Feb 2011 07:43:22 GMT

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

RE: Unused Indexes in your databases

randal.schmidt — Wed, 23 Feb 2011 06:55:50 GMT

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

Unused Indexes in your databases

harriga.rabie-1008938 — Tue, 22 Feb 2011 20:51:17 GMT

Comments posted to this topic are about the item [B]Unused Indexes in your databases[/B]