From time to time, somebody needs to find some information about the indexes in the database.  As DBA’s, there are a few tools at your disposal to look up that information.  The required/requested information is not always the same.  That said, you can usually adapt a query quickly to find what you need.

Where to Look

SQL 2005 and up have provided us with some very useful means to gain insight into the indexes in our databases.  The following DMOs is a small representation of that information.

• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_groups
• sys.dm_db_index_usage_stats
• sys.dm_db_index_physical_stats

Add to that, there are some catalog views to help with finding information relevant to indexes in the databases.

• sys.indexes
• sys.index_columns

With just these few objects, there is a wealth of information at your fingertips.  I often find myself querying for information about indexes.  I also find myself being asked how to find this information on a relatively frequent interval.  Usually, I just end up writing out the query again (depends on mood and day).

Rewriting the query doesn’t take too much time when you know what you need to query.  That said, sometimes it is nice to have a base query ready to go.  From this query you can add/remove items as you see fit to get the information that you really need.

For instance, here is a query to get some of the fragmentation information.  I would probably just re-use this query over and over where applicable – only changing the parts that mattered to get the necessary result set.

Code block

SELECT OBJECT_NAME(ps.OBJECT_ID),I.name,index_level,index_type_desc
,I.is_hypothetical,ps.page_count,ps.record_count,ps.avg_fragmentation_in_percent
,ps.ghost_record_count
,ps.compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),null,null,null,'detailed') ps
INNER Join sys.indexes I
ON ps.OBJECT_ID = I.OBJECT_ID
And ps.index_id = I.index_id
ORDER BY OBJECT_NAME(ps.OBJECT_ID),ps.index_id,ps.index_level

There are many types of queries we could write for the different relevant information pertaining to our indexes.  It is all a matter of knowing where to start.  I hope that knowing about these DMOs and catalog views, it will help shorten your efforts to retrieving this information.