SQL-Quick tip #13 - Index usage

Sql Server tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Index usage

It should be common knowledge that when you use a Sql Server database you should use indexes to ensure that your database is performing optimally. Choosing the the correct indexes is kind of an art when you work with big databases with lots of tables because you need to add just the right amount of indexes. Too few indexes and you will not get the optimal performance, but too many indexes can actually slow down the performance.
The script below will show you the seeks, scans, lookups and updates for the current database and you can use that information to determine if an index is not being used.

SELECT OBJECT_NAME(sts.[OBJECT_ID]) AS [Table name],
       idx.[NAME] AS [Index name],
       user_seeks,
       user_scans,
       user_lookups,
       user_updates
  FROM sys.dm_db_index_usage_stats AS sts
  JOIN sys.indexes AS idx ON idx.[OBJECT_ID] = sts.[OBJECT_ID] AND idx.INDEX_ID = sts.INDEX_ID
 WHERE OBJECTPROPERTY(sts.[OBJECT_ID],'IsUserTable') = 1
   AND sts.database_id = DB_ID()