One book to read is Relational Database Index Design and the Optimizers by Tapio Lahdenmaki and Mike Leach.

This Book contains basics and a "little" further.

Regards

JT

a DBA

The only comment I'd make is that the order of columns in a covered index "just depends" , generally the most restrictive ( selective) is best placed first - but not always - so it's worth making a few tests including putting the columns in reverse order, I tend to create a number of variations for a particular query and see which the optimiser finds best ( not for simple queries or all queries obviously ) Also worth noting that changing data volumes can change the index selection process.

Nice article.

Great Article!  You know what would make your query even more valuable (to me, at least) is a rowcount for the table in the resultset.  The indexes I would want to pay the most attention to for potentially dropping are the indexes on the largest tables.  I would add that to the order by.  Anyone know how to get the rowcount in 2005?  The rowcnt field that was available on sysindexes doesn't appear to have made the transition from 2000 to 2005.

Luke

Luke-

Try joining to sys.dm_db_index_physical_stats to determine rowcount... that should work.

(post-coffee)

Wait, that's a function...  try APPLY.

(mid-coffee)

a join seemed to worked, I think...  would it have been better to use APPLY? TIA, I've never tried it.  And thank you for the article!

>L<

SELECT o.name AS object_name, i.name AS index_name,
i.type_desc, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates,
p.record_count
FROM sys.indexes i
JOIN sys.objects o
ON i.object_id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats u
ON i.object_id = u.object_id
AND i.index_id = u.index_id
AND u.database_id = DB_ID()
JOIN (SELECT object_id,record_count
FROM master.sys.dm_db_index_physical_stats(DB_ID(), NULL,NULL,NULL,'DETAILED') ) p
ON i.object_id = p.object_id
WHERE o.type <> 'S' -- No system tables!
ORDER BY (ISNULL(p.record_count,0) +ISNULL(u.user_seeks, 0) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0) 
+ ISNULL(u.user_updates, 0)), o.name, i.name

Great article with many good tips.

You said, "I once had a developer look me straight in the eye and say that there would never be a problem with adding as many indexes as possible to a table; the query processor would simply select the ones it needed and ignore the rest."  That might be OK if this is a pre-built, static, never changing database.  Too many indexes are going to cause a bad impact on INSERT, UPDATE, and DELETE.  Remember all those indexes have to be updated when the data changes.

Yup... see my comment two lines down:

Unnecessary indexes can force the query processor to evaluate more before deciding on an optimum execution plan, can slow down transactional processing and can take up a great deal of space on disk.