When I'm looking over a troublesome query for tuning options I find it important to have the full picture of what other indexes already exist on the tables in my query. I'd want this in order to determine one or more of the following situations before proceeding with the possibility of adding (yet) another index:
- Is there already another index that is identical to the one I'm considering creating? If so, then why is it not being selected by the Query Optimizer (QO) for use?
- Is there a similar index that already exists on the table? If so then perhaps I could tweak it in order be useful for this query in addition to the queries where it's already useful. (Of course we'd also want to look at index usage to determine if it is even being used for other query plan executions.)
- Is this table suffering from too many indexes?
- Is this table a heap - meaning it has no clustered indexes applied to it.
All of these questions can be addressed by querying three system catalog views: sys.indexes, sys.index_columns and sys.all_columns:
- sys.indexes provides metadata relating to all indexes in a database
- sys.all_columns exposes information about all the columns in all tables and views in a database
- sys.index_columns serves as an intermediary - relating columns to indexes based upon their index_id, object_id, and column_id
--=============================================
--What indexes are on this table?
--=============================================
SELECT OBJECT_NAME(I.[object_id]) AS [object_name]
, I.name AS index_name
, IC.index_column_id
, AC.name AS column_name
, IC.[is_descending_key]
, IC.is_included_column
, I.type_desc AS index_type
, I.is_primary_key
, I.is_unique
, I.is_unique_constraint
FROM sys.indexes I
INNER JOIN sys.index_columns IC
ON I.index_id = IC.index_id
AND I.[object_id] = IC.[object_id]
INNER JOIN sys.all_columns AC
ON IC.[object_id] = AC.[object_id]
AND IC.column_id = AC.column_id
WHERE I.object_id = OBJECT_ID('<table__name,, tblFoo>')
ORDER BY I.is_primary_key DESC
, I.name
, IC.is_included_column
, IC.index_column_id;
--=============================================
-- By Tim Ford, SQL Cruise (www.sqlcruise.com)
--=============================================
You'll notice that I employed a template parameter for the table name. Simply use the keyboard shortcut of Cntl+Shift+M to replace that value with the appropriate table name. Further information on template parameters and the Template Explorer can be found here.
The query results provide us with the following information to make educated choices in how to proceed:
- Indentifying information such as index name and column name
- Index structural information: clustered or non-clustered index
- Column information: ordering, uniqueness, whether it's an included column or not
- Reference information: is the index used in a primary key constraint or unique constraint?
All things valuable in making decisions about how to proceed in performance tuning when indexing changes are one of the options on the (ahem) "table".
