Run above query. primary keys and statistics are eliminated.
Usability is a percentage; 'used' against 'modificated'.
Sort this in the way you like (the only thing you have to add).
select LEFT(object_name(a.object_id),40) "tabel",
LEFT(b.name,100) "index",
b.keycnt "columns",
b.used/128 "MB",
(a.user_seeks + a.user_scans + a.user_lookups) "used",
user_updates "modificated",
round(convert(float,convert(float,(a.user_seeks + a.user_scans + a.user_lookups)) / convert(float,(user_updates))) * 100,0) "usability"
from sys.dm_db_index_usage_stats a, sysindexes b
where a.database_id = db_id()
and a.index_id > 1
and a.object_id = b.id
and a.index_id = b.indid
and b.name not like 'PK%'
and b.name not like '[_]WA[_]%'
and a.user_updates > 0 
This SP reports the status of your heap tables. It gives you an overview of fragmentation, ghost record counts and forwarded record counts. Another reason to create a clustered index!
2010-04-09 (first published: 2010-03-09)
2,150 reads
Searches for foreign key constraints that don't have fully matching indexes. The best partial matching indexes are outputted with MatchCounts and column comparisons.
2009-10-15 (first published: 2009-10-01)
1,868 reads
Finds the largest dupe-sets of one or more columns of a single table or derived table. Great for finding hoggy parameter combinations for perf-testing queries within stored procedures!
2009-05-29 (first published: 2009-05-05)
913 reads
2009-02-05 (first published: 2008-05-20)
2,204 reads
Andy did "Under the Covers" last week, what the heck will it be next week? Read this article to see how maintenance plans update statistics, rebuild indexes, and remove free space from your databases. Add your comments to the article, maybe even a suggestion or two for better titles!
2002-05-27
10,422 reads