Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Unused Indexes in your databases Expand / Collapse
Author
Message
Posted Tuesday, February 22, 2011 8:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 8:19 AM
Points: 13, Visits: 284
Comments posted to this topic are about the item Unused Indexes in your databases
Post #1068035
Posted Wednesday, February 23, 2011 6:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 30, 2014 7:53 AM
Points: 33, Visits: 117
There are two ORDER BY clauses in a row. The script does not work.
Post #1068224
Posted Wednesday, February 23, 2011 7:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, June 28, 2014 4:53 PM
Points: 60, Visits: 177
Also two where clauses. Fixed the two duplicates, but still have a page of error messages.
Post #1068267
Posted Wednesday, February 23, 2011 8:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 12:55 PM
Points: 169, Visits: 112
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
Posted Wednesday, February 23, 2011 8:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 8:19 AM
Points: 13, Visits: 284
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
Posted Wednesday, February 23, 2011 9:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 1, 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
Posted Wednesday, February 23, 2011 1:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:47 PM
Points: 150, Visits: 591
There are several errors in the code as presented. Please clean your code before you publish it.
Post #1068521
Posted Thursday, February 24, 2011 5:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 8:19 AM
Points: 13, Visits: 284
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
Posted Thursday, March 17, 2011 4:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 1,713, Visits: 6,243
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
Posted Thursday, March 17, 2011 6:42 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, April 8, 2013 8:13 AM
Points: 700, 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 »

Add to briefcase 12»»

Permissions Expand / Collapse