As you're probably aware, one of the most important tasks that the SQL Server query optimizer performs is to determine which index (or set of indexes)
to use for each table in a query. As each query is being optimized, SQL Server keeps
track of indexes that the optimizer determines might have been useful by establishing
the best possible index for each search argument in your WHERE clause and for each
lookup done through a join. SQL Server 2005 then makes information about missing
indexes available to you through two components. First,
SQL Server provides a set of metadata objects to supply
information about missing indexes. Second, for each
query, information about missing indexes is available
in the XML query plan. I'll explain the missing-index metadata, which can help you
improve query performance, and next month I'll provide more details about working
with the missing-indexes feature, including accessing missing-index information in
your XML query plans.
Missing-Index Metadata
SQL Server has four dynamic management objects that provide information about
missing indexes.
dm_db_missing_index_details. This view returns one row for each index that the
optimizer determined was missing and includes information about columns that should
be created as key columns and columns that can be added to the index as included
columns. (For more information about SQL Server 2005's "included columns" feature,
see "Seek and You Shall Find," September 2005, InstantDoc ID 46929 and "10 Things
to Love About SQL Server 2005," May 2005, InstantDoc ID 45930.) Each missing
index is identified by a unique value called an index_handle.
dm_db_missing_index_group_stats. Missing indexes each belong to an index group,
even when a group has only one index. In fact, in SQL Server 2005, a group will never
have multiple indexes. Each index group appears in this view along with information
about how many times this missing-index group could have been used and what kind
of performance improvement might be expected if the indexes in the group were created. Each missing-index group is identified by a unique value called a group_handle.
dm_db_missing_index_groups. This view maps index_handle values to group_handle
values; in this view, the missing-index–group values are in a column called index_group_
handle. Because there are no index groups with multiple indexes, you should see a one-to-one relationship between missing
indexes and missing index groups.
dm_db_missing_index_columns. This
is the only function in the list and requires
an index_handle as a parameter. The function returns a table containing a list of
suggested columns needed to build the
specified missing index.
Dissecting the Metadata
To delve into the metadata, first run the
code in Listing 1, which makes copies of
three tables from the AdventureWorks
database and builds no indexes on them.
Now run the query in Listing 2 and
note the performance information. I'm
running SQL Server 2005 with the
November 2006 Community Technology
Preview (CTP) of Service Pack 2 (SP2),
and I get the results that Figure 1 shows
for the logical reads component of STATISTICS IO. I get the results that Figure 2 shows for the execution-time component
of STATISTICS TIME. The query plan
indicates that all three tables are being
scanned and that there's one loop join with the very small dbo.Territory table as
the outer table and a second, hash join.
We can now inspect the dm_db_missing_
index_details view. As of SP2, SQL Server
allows an optional second parameter for the
object_name function. If you're still using
SP1, you can invoke object_name with only
the first parameter, as long as you're in the
AdventureWorks database.
The query results in Table 1 show two
recommended indexes, each of which SQL
Server could use in a query that compares a
single column to a constant. In the dbo.Customers table, we're looking for a particular
TerritoryID value—2 in this example. The
query also uses TerritoryID as the join key
with the Territory table, so when Customers
is the inner table in that join, the lookup to find the matching
rows uses the TerritoryID column. The
query uses the dbo.OrderHeader table as the inner table in a join, searching for matches on the CustomerID column, so the optimizer also recommends building an index on the
CustomerID column of the dbo.OrderHeader table.
Prev. page
[1]
2
next page 
