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.
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
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