My current project has hit that interesting point where the database design is pretty stable and we're starting to go at the objects in multiple directions with code.  I'm really outside my comfort zone this time as we're doing predominantly LINQ to SQL for the application.  I suspect I will have a series of posts about those experiences.  But back to the topic...I usually try to avoid "guessing" about most indexing for my tables and let the access pattern of the application present itself.  Since I'm not writing all the code interacting with these tables I need another manner of finding these patterns.

In SQL Server 2005 the product team introduced the Missing Index Feature.  The Missing Index Feature consists of two components:

• A set of four dynamic management objects that can be queried to return information about missing indexes.
• The MissingIndexes element in XML Showplans, which correlate indexes that the query optimizer considers missing with the queries for which they are missing

The feature is on by default and can only be disabled by starting your instance of SQL Server using the -x argument with the sqlservr command-prompt utility.

For our purposes I'm going to focus on the DMVs and show how we can get some suggestions about where our database might improve with additional indexes.  Note the use of "suggestions" and "might improve". The information gathered by the DMVs is only accurate from the last restart of the service so the detail could vary wildly in sample size ( if you don't have a set period for restarts ).

Here is our query:

SELECT sc.name AS schemaName
    ,t.name AS tableName   
    ,d.equality_columns AS equalityColumns
    ,d.inequality_columns AS inequalityColumns
    ,d.included_columns AS includedColumns
    ,s.user_seeks AS userSeeks
    ,s.user_scans AS userScans
    ,s.avg_user_impact AS averageUserImpact
FROM sys.dm_db_missing_index_details AS d 
    INNER JOIN  sys.dm_db_missing_index_groups AS g 
        ON d.index_handle = g.index_handle 
    INNER JOIN sys.dm_db_missing_index_group_stats AS s 
        ON g.index_group_handle = s.group_handle 
    INNER JOIN sys.tables AS t 
        ON t.[object_id] = d.[object_id]
    INNER JOIN sys.schemas AS sc 
        ON sc.[schema_id] = t.[schema_id]
WHERE d.database_id = DB_ID() 
ORDER BY avg_user_impact DESC;

The main areas we want to take note of are equality, inequality, and included columns.  These will form the basis of our key decision.  We also can get the number of seeks and scans as well as an average statement of impact to the queries in our current workload.

Again, make sure you look at the overall access patterns of your application before running wild with a bunch of CREATE INDEX scripts.  As I pointed out in my indexing series a few weeks back, a bad index can create just as many problems as no index.

There are multiple limitations to the Missing Index Feature discussed here.  It is required reading before adding on to the query above for creating indexes.

If you have questions feel free to drop me line.  Happy indexing to ya...