SQL Server 2005 provides metadata about missing indexes through four dynamic
management objects. Last month in "Uncovering Missing Indexes," InstantDoc ID
94884, we examined two of those objects, the sys.dm_db_missing_index_details
view and the sys.dm_db_missing_index_columns function, and walked through sample
queries that generated missing_index information to populate the dynamic management
objects. Let's continue exploring the missing-index feature and discuss the
other two missing-index dynamic management views, sys.dm_db_missing_index_group_stats
and sys.dm_db_missing_index_groups, which provide information about missing-index
groups. As with the other two dynamic management objects covered last month,
you can use the information obtained from sys.dm_db_missing_index_group_stats
and sys.dm_db_missing_index_groups to identify missing indexes that could be
created to improve query performance. As you'll see, you can also use SQL Server
2005's XML Showplan feature to view missing-index information in XML format.
Mapping Indexes to Index Groups
Missing indexes each belong to an index group, even if the group has only one
index. In fact, in SQL Server 2005, you'll never find multiple indexes in a
group. Each index group appears in the dm_db_missing_index_group_stats view
along with information about how many times this missing-index group could have
been used and what kind of performance improvement you might expect if the indexes
in the group were created. Each missing-index group is identified by a unique
value called a group_handle. Microsoft plans to include the ability to indicate
indexes that need to be combined into a group in a future version of SQL Server.
This capability is intended to deal with queries that require the use of multiple
indexes for optimal performance. SQL Server 2005 can provide recommendations
based on only one index.
To map the missing indexes that you saw last month in sys.dm_db_missing_ index_details
to a missing-index group, so that you can obtain information about how useful
such an index might be, the dm_db_missing_index_groups view serves as a mapping
table to map index_ handle values to group_handle values. Because there are
no index groups with multiple indexes in SQL Server 2005, you should see a one-to-one
relationship between missing indexes and missing_index groups. To obtain the
missing-index data, start by running the script in Listing
1. This script creates the same three tables in the AdventureWorks database
that I used last month, then executes a SELECT query by using those three unindexed
tables.
Examining sys.dm_db_missing_index_ details should show you two missing indexes,
just as we saw last month. We can now join sys.dm_db_missing_index_ details
to the two other views by using the code below.
SELECT * FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
(Some code in this article wraps to multiple lines because of space constraints.)
The output is too wide to show here, but the key column values to look at for
performance information are
- unique_compiles—shows the number of compilations and recompilations
that would have benefited from this missing-index group. These compilations
don't all have to be the same query; many different queries can potentially
make use of the suggested indexes.
- user_seeks—shows the number of seek operations performed in
user queries for which the recommended index in the group could have been
used.
- user_scans—shows the number of scan operations performed in
user queries for which the recommended index in the group could have been
used.
- avg_user_impact—shows the average percentage improvement that
user queries might have if the missingindex group were implemented. On average,
all user queries (seeks and scans) that use an implemented missing-index group
would show in a decrease in this percentage in their execution cost.
Of course, there are no guarantees about the actual improvement you'll realize
if you build the suggested missing indexes. These values are all estimates derived
by the SQL Server optimizer when it optimizes your queries.
Missing-Index Data Is Missing
The missing indexes feature is intended to give you a head start on query tuning
by using SQL Server's own optimization process to generate a list of possible
ways to improve your queries. However, not all queries, even those that might
benefit greatly from a new index or two, will generate data in the missing-index
dynamic management objects.
At present, the missing-index information is generated only for queries that
meet these conditions:
- The query must have a predicate (i.e., a WHERE clause) referencing a column
with a potential missing index.
- The optimizer must not consider the query plan to be trivial.
A trivial plan is one for which the optimizer determines there's only
one possible solution. For example, if you have a query that's trying to find
rows in a single table with no indexes, no matter how many predicates you have,
there's only one possible plan: SQL Server must scan the entire table to find
the rows you requested. So although a query like this might take a long time,
and greatly benefit by indexes, the optimizer doesn't store costing information
for such a query—and it's costing information that determines whether
missing-index information is collected and stored.
To see what I mean, create another table in the AdventureWorks database by
running the code below.
USE AdventureWorks;
GO
IF EXISTS (SELECT 1 FROM
sys.tables
WHERE name = ‘Orders')
DROP TABLE Orders;
GO
SELECT * INTO dbo.Orders
FROM Sales.SalesOrderDetail;
GO