If the sys.dm_db_missing_index_
details view showed recommendations for inequality_columns only, indexes
built on those columns would probably be less useful and the search condition would be less selective. If the sys.dm_db_missing_index_details view shows
recommendations for both equality_columns and inequality_columns, we should
create the index using the equality_columns first. The included_columns that sys.dm_db_missing_index_details shows are
columns that you could add to the index
definition to give you a covering index. In
my experience, these columns don't have
be actual "included columns." Rather, these
columns could be additional key columns
of the index.
The dm_db_missing_index_columnsfunction returns the
list of all the columns
to include in a specific
suggested index, when
the function is passed
an index_handle as an
argument. My query
in Listing 3,
which selects from thesys.dm_db_missing_
index_details view,
doesn't include the
index_handle, so as to
limit the output width,
but you could easily
rewrite the query to
include index_handle.
My query returned an
index_handle value of
13 for the index on
the dbo.Customers table. I can then call the dm_db_missing_
index_columns function using that value, as
the following query shows (code lines wrap
because of space constraints), and see the
results that Table 2 shows. This is basically
the same information that we saw in the
sys.dm_db_missing_index_details view, but
we see the columns for only a single index,
and each suggested column in the index is
in its own row.
SELECT * FROM sys.dm_db_
missing_index_columns(13);
In a first attempt to improve this query's
performance, run the script in Listing 4 to
build two indexes using the equality_columns as keys. Then rerun the query in Listing 2; I observed the STATISTICS IO
and STATISTICS TIME information that Figure 3 shows when I reran the query.
We can see that not only is no Worktable
required, but the number of reads in the
dbo.OrderHeader table has been reduced.
The plan still shows two table scans—but
no hash join, and the new index on dbo.
OrderHeader is being used. However, the
new index on dbo.Customers isn't used.
Apparently, merely building the index on
dbo.Customers on a single column was
insufficient. Let's run the code in Listing 5 to
rebuild the index on dbo.Customers using
all the columns, as already seen in Table 2.
Run the query in Listing 2 one more
time. Note that this time the index on dbo.Customers is being used. You'll see the the STATISTICS IO and STATISTICS TIME
values that Figure 4 shows. This latest plan
indicates that the only table scan is on the
outermost table in the query, and it's by far smallest table, with
only two pages total.
There are fewer reads and the query takes less time to run, but the improvement isn't as great as the improvement after building
the two indexes initially.
How Long Is the Index Missing?
The data is available through the missing-index dynamic management objects until
SQL Server is restarted or until there's
a change to the table schema, such as
adding or removing columns or changing
a column's data type. Also actually building
an index suggested in the missing-index
metadata removes the corresponding row
from the view. In addition, SQL Server can
keep track of a maximum of 500 individual
indexes in the sys.dm_db_missing_index_
details view. Through SQL Server 2005 SP1, the optimizer stops saving missing-index
recommendations once the 500-index limit
is reached. SP2 includes logic to remove 20
percent of the less-relevant index suggestions when the 500-index limit is reached,
so that over time the metadata should
contain the most relevant information for
the workload.
Keep in mind that the missing-index
metadata isn't intended to replace the
SQL Server 2005 Database Engine Tuning
Advisor or your own index analysis. However, the metadata provides some quick
suggestions to help you improve the performance of queries that lack only a single
index on a search or join column.