Unused Indexes in your databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Rabie Harriga » Unused Indexes in your databases

15 posts, Page 1 of 2

1 2 »»

Unused Indexes in your databases

Rate Topic

Display Mode

Topic Options

Author

Message

harriga.rabie-1008938

Posted Tuesday, February 22, 2011 8:51 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 8:54 AM
Points: 10, Visits: 251

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

Post #1068035

randal.schmidt

Posted Wednesday, February 23, 2011 6:55 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, October 26, 2012 9:46 AM
Points: 33, Visits: 116

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

Post #1068224

gitmo

Posted Wednesday, February 23, 2011 7:43 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, April 18, 2013 2:24 PM
Points: 59, Visits: 161

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

Post #1068267

bustell

Posted Wednesday, February 23, 2011 8:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:45 PM
Points: 168, Visits: 97

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

Post #1068287

harriga.rabie-1008938

Posted Wednesday, February 23, 2011 8:28 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 8:54 AM
Points: 10, Visits: 251

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

Post #1068309

mgigani-696113

Posted Wednesday, February 23, 2011 9:08 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 01, 2013 10:50 AM
Points: 23, Visits: 57

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

Post #1068346

Dave Vroman

Posted Wednesday, February 23, 2011 1:41 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:13 AM
Points: 46, Visits: 204

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

Post #1068521

harriga.rabie-1008938

Posted Thursday, February 24, 2011 5:58 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 8:54 AM
Points: 10, Visits: 251

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

Post #1068864

Toreador

Posted Thursday, March 17, 2011 4:35 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 1,356, Visits: 4,761

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

Post #1079549

Jaroslav Pfauser

Posted Thursday, March 17, 2011 6:42 AM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, April 08, 2013 8:13 AM
Points: 688, Visits: 100

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

Post #1079607

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions