Gathering SQL Server Index Statistics and Usage Information

After creating the indexes, we should proactively know which indexes are badly used, or totally unused in order to perform the correct decision to maintain these indexes or replace it with more efficient ones. Recall that removing the unused indexes or badly indexes will improve the performance of the data modification queries, that needs to replicate the same table change to the indexes, and reduce the index maintaining and storing overhead.

3 Ways to Gather Index Information

• Expanding the Indexes node under the database tables, then right-clicking on each index, and choose the Properties option.
• sp_helpindex system stored procedure.
• Querying the sys.indexes system dynamic management view.

SELECT  
	  Tab.name AS Table_Name 
	, IX.name  Index_Name
	, IX.type_desc Index_Type
	, Col.name AS Index_Column_Name
	, IXC.is_included_column Is_Included_Column
	, IX.fill_factor 
	, IX.is_disabled
	, IX.is_primary_key
	, IX.is_unique			 		  
FROM  
	sys.indexes IX 
    INNER JOIN sys.index_columns IXC ON IX.object_id = IXC.object_id AND IX.index_id = IXC.index_id  
    INNER JOIN sys.columns Col ON IX.object_id = Col.object_id AND IXC.column_id = Col.column_id     
    INNER JOIN sys.tables Tab ON IX.object_id = Tab.object_id

Sources:

Gathering SQL Server indexes statistics and usage information