This TSQL query returns a huge amount of useful information about indexes. You can see how much an index is used, how much space it is taking up, what columns are included, etc. It is a great first step towards optimizing a database. For example, if an Index has a lot of Writes, but few Reads, it should probably be dropped (as it is costing you on inserts, but not providing much benefit on reads).

To use, just change the @dbName variable to be whatever your database is named.

Credit to Pinal Dave, I’m sure parts of this came from his blog, though I can’t say for sure which. His blog is a great source for SQL snippets, one of the places I always tend to find what I’m looking for. http://blog.sqlauthority.com/

DECLARE @dbName NVARCHAR(100)
SET @dbName = 'dbname';

WITH physical AS  
(
	SELECT 
	avg_fragmentation_in_percent
	, fragment_count
	, page_count
	, avg_page_space_used_in_percent
	, record_count
	, avg_record_size_in_bytes
	, object_id
	, index_id
    FROM sys.dm_db_index_physical_stats(DB_ID(@dbName), null, null, null, 'Sampled')
)
SELECT  o.name  AS 'Table'
        ,i.name AS 'Index Name'
        ,i.Type_Desc AS 'Index Type'
        ,user_seeks AS 'Seeks'
		,user_scans AS 'Scans'
		,user_lookups AS 'Lookups'
		,user_seeks + user_scans + user_lookups as 'Total Reads'
		,user_updates AS 'Updates'
		,CASE WHEN user_updates = 0 
		      THEN null 
		      ELSE ((user_seeks + user_scans + user_lookups)  / CAST(user_updates AS FLOAT))
		  END AS 'ReadToWriteRatio'
		,IndexColumns.IndexedColumns AS 'Indexed Columns'
		,Included.IncludedColumns AS 'Included Columns'
		,i.filter_definition AS 'Filter Expression'
		,IndexSize.IndexSizeInKB AS 'Index Size kb'
		,physical.avg_fragmentation_in_percent AS 'Fragmentation Percent'
		,physical.fragment_count AS 'Fragment Count'
		,physical.page_count AS 'Page Count'
		,physical.avg_page_space_used_in_percent AS 'average page space used percent'
		,physical.record_count AS 'Record Count'
		,physical.avg_record_size_in_bytes * 8 AS 'Avg Record Size Kb'
 FROM sys.objects o JOIN sys.indexes i
	ON o.object_id = i.object_id
 LEFT OUTER JOIN sys.dm_db_index_usage_stats s   
	ON i.object_id = s.object_id  
	AND i.index_id = s.index_id 
 LEFT OUTER JOIN physical ON physical.object_id = o.object_id AND physical.index_id = i.index_id
 OUTER APPLY 
 (
	SELECT SUBSTRING
	(
	  (
		SELECT ',' +  col.NAME + CASE WHEN ic.is_descending_key  = 1 THEN ' DESC ' ELSE '' END
		FROM sys.Indexes ix
		JOIN sys.Index_Columns ic 
		on ix.Index_ID = ic.Index_Id
		and ix.object_id = ic.object_id
		INNER JOIN sys.columns col 
		ON ic.object_id = col.object_id and ic.column_id = col.column_id 
		and ic.is_included_column = 0
		WHERE ix.Object_ID = o.object_id
		and ix.Index_ID = i.Index_id
		ORDER BY ic.index_column_id ASC
		FOR XML PATH('')
	  ),2,200000
	 ) as 'IndexedColumns'
  ) IndexColumns
   OUTER APPLY 
  (
     SELECT SUBSTRING
     (
		(
		SELECT ',' +  col.NAME  FROM sys.Indexes ix
		JOIN sys.Index_Columns ic 
		ON ix.Index_ID = ic.Index_Id
		AND ix.object_id = ic.object_id
		INNER JOIN sys.columns col 
			ON ic.object_id = col.object_id and ic.column_id = col.column_id 
		AND ic.is_included_column = 1
		WHERE ix.Object_ID = o.object_id
		AND ix.Index_id = i.index_id
		FOR XML PATH('')
		),2,200000
	 ) AS 'IncludedColumns'
  ) Included
  OUTER APPLY 
  (
    SELECT 8 * SUM(a.used_pages) AS 'IndexSizeInKB' FROM
	sys.partitions AS part 
	JOIN sys.allocation_units AS a ON a.container_id = part.partition_id
	WHERE part.Index_id = i.index_id and part.object_id = i.object_id
	GROUP BY part.OBJECT_ID, part.Index_ID
  ) IndexSize
WHERE 
 o.type = 'u'AND 
 i.type IN (1, 2)
-- Clustered and Non-Clustered indexes
AND s.database_id = DB_ID(@dbName) 
AND   OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 
ORDER BY o.name