February 22, 2011 at 8:51 pm
Comments posted to this topic are about the item Unused Indexes in your databases
February 23, 2011 at 6:55 am
There are two ORDER BY clauses in a row. The script does not work.
February 23, 2011 at 7:43 am
Also two where clauses. Fixed the two duplicates, but still have a page of error messages.
February 23, 2011 at 8:02 am
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
February 23, 2011 at 8:28 am
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
February 23, 2011 at 9:08 am
I have a question.
Does this statistics persist after a SQL Server service restart? or server reboot etc.
February 23, 2011 at 1:41 pm
There are several errors in the code as presented. Please clean your code before you publish it.
February 24, 2011 at 5:58 am
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
March 17, 2011 at 4:35 am
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).
March 17, 2011 at 6:42 am
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
August 23, 2011 at 8:41 am
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.
August 23, 2011 at 8:57 am
I am getting an error when I add that in (invalid column name dpages). Can you post the complete SQL?
August 23, 2011 at 9:19 am
@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
August 23, 2011 at 9:23 am
Thank you. That took care of my problem.
March 30, 2013 at 6:25 am
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