Use Missing-Index Groups for Query Tuning

SQL Server 2005 provides metadata about missing indexes through four dynamic management objects. Last month in "Uncovering Missing Indexes," 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

Although the following query (Query 1) contains a predicate on a column that might benefit from an index, the optimizer determines that the plan for this query is trivial, so no missing-index information is generated.

SELECT * FROM orders 
WHERE SalesOrderDetailID = 40104;

The next query, Query 2, doesn't have a trivial plan, but it has no predicates, so again, no missing-index information is reported.

SELECT ProductID, count(*)
  FROM orders
  GROUP BY ProductID;

However, if you combine queries 1 and 2 into a third query (Query 3), you get a nontrivial plan that includes a predicate, and so you'll see additional information in the sys. dm_db_missing_index_details view.

SELECT ProductID, count(*)
  FROM orders
  WHERE SalesOrderDetailID = 40104
  GROUP BY ProductID;

Viewing an XML Query Plan

You might be wondering how you can determine whether the optimizer considers a plan trivial. You can find the answer by looking at the new XML Showplan output, which is available in SQL Server 2005. You can either use SET SHOWPLAN XML ON to obtain an estimated XML plan for a query without actually running it, or you can use SET STATISTICS XML ON to obtain an XML plan for a query as you execute it. For example, before running Query 1, I can use the following code to turn on the XML STATISTICS option:

SET STATISTICS XML ON;
  SELECT * FROM orders
  WHERE SalesOrderDetailID = 40104;

The query returns the results as usual but also includes a link to an XML document. You have to be using the SQL Server Management Studio (SSMS) query feature that displays your results in "grid" mode to obtain an actual link. If you're displaying your results in text mode, you'll see the entire XML document in one long string in the results window. I don't recommend using that method. In grid mode, you'll get a link that looks something like Figure 1.

When you click the link, another tabbed window will open, showing the entire XML document for the query plan with separate nodes for each query element. Near the top of the output, you should see XML that looks something like that in Figure 2. The statement-optimization level is reported as "TRIVIAL," which means that no missingindex information will be generated.

The XML document for the plan for Query 2 includes the excerpt that Figure 3 shows. Although this plan isn't trivial, the query contained no predicates, so there's no missing-index information.

If you look at the XML plan for Query 3, however, not only will you notice that the statement optimization level is "FULL," but missingindex information is generated. You can see that information by examining the missingindex dynamic management views, or you can look more deeply into the XML plan document, in which you'll see the data that Figure 4 shows. Certain information, such as optimization level, isn't available in any documented form other than the XML query-plan output. In an upcoming column, I'll cover the XML Showplan output in more detail.

Controlling the Missing-Index Feature

The missing-index feature is on by default, and SQL Server provides no controls or configuration options to adjust it. You can't clear the information in the dynamic management views except by stopping and restarting your SQL Server service. No controls are provided to turn the missingindex feature on or off or to reset any of the tables returned when the dynamic management objects are queried. When SQL Server is restarted, it loses all the missing-index information.

You can disable the missing-index feature by starting an instance of SQL Server using the -x argument. But the documentation for the Sqlservr utility (sqlserver.exe) doesn't mention that -x affects missing-index information. It states only that -x disables the keeping of CPU time and cache-hit ratio statistics. So keep in mind that if you enable -x to avoid any overhead from accumulated missing-index information, you'll also keep SQL Server from gathering other performance-related statistics.