SQL Server Magazine

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.

End of Article

Prev. page     1 [2]     next page -->