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 AS Table_Name 
	,  Index_Name
	, IX.type_desc Index_Type
	, AS Index_Column_Name
	, IXC.is_included_column Is_Included_Column
	, IX.fill_factor 
	, IX.is_disabled
	, IX.is_primary_key
	, IX.is_unique			 		  
	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


Last modified: March 5, 2020



Write a Reply or Comment