DMV to find missing indexes

I found the following SQL intended to help identify missing indexes. I'd like to better understand the output.

   SELECT   migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS ExpectedImprovement
           , DB_NAME(mid.database_id)            AS DBName
           , mid.[object_id]                     AS ObjectID
           , mid.statement                       AS FullyQualifiedObjectName
           , ISNULL (mid.equality_columns,'开发者_Python百科') +
             CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN
                ','
             ELSE
                ''
             END +
             ISNULL (mid.inequality_columns, '') AS IndexColumns
           , ISNULL (mid.included_columns, '') AS IncludeColumns
           , migs.user_seeks
           , migs.user_scans
           , migs.last_user_seek
           , migs.last_user_scan
      FROM sys.dm_db_missing_index_groups      mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details     mid  ON mig.index_handle = mid.index_handle
     WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
  ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

One of the returned columns is "Include Columns".

I was wondering if this meant that the column names mentioned should be added to the list of columns in the index or whether there is some other feature to return those presumably high accessed column value without adding them to the index itself. (Perhaps I imagined hearing of such a feature or maybe it was another vendor's db)

Is the output just suggesting that the list of columns in the index be expanded by the columns mentioned in the "Include Columns" returned value? I am reacting to output suggesting that I add 15 fields to an index and this seems potentially excessive to me.

A non-clustered index contains both a list of columns that are indexed and a list of columns that are "included" for quick-reference.

For example, lets say you have a table with 100 columns, but you almost exclusively query 5 of them, predicating on the date. e.g.

select col1, col2, col3, col4, YourDate from YourTable where YourDate = '???'

If you indexed only the date column, you would have to do what is called a RowID Lookup (or Bookmark Lookup). You'll efficiently find the desired rowset, but you'll need to return to the physical table to get the other 4 columns (col1-col4) you want to select.

If you create the index with an included column list, those columns are stored in the index for exactly that purpose.

create nonclustered index ix_YourTable on YourTable (
     YourDate)
include (
     col1, col2, col3, col4)

Now you don't return to the table to get col1-4, you read them directly from the index. Obviously this increases the amount of storage required for that index.

You should also be aware that if your frequent query changes to

select col1, col2, col3, col4, col5, YourDate from YourTable where YourDate = '???'

You'll still be using the index, but you'll be doing the RID Lookup in order to get the data for col5. You need to add col5 to your index's include list in order to reference it efficiently.

Hope that helps!

SQLServers lets you "include" non-key columns (See details here). The main difference between key columns and non-key columns in the index is that non-key columns are stored only in the leaf level of the index in contrast to key columns which are stored at all levels.

I wouldn't recommend to follow advise of your script output (your script is good, don't get me wrong) literally, i.e. you don't really have to create all the suggested indexes and include all the columns it wants from you. From my experience SQLServer wants you to make all your query to have a covering index which may affect performance of update/insert and increase database size.