Databases Reference
In-Depth Information
Run this query and request a graphical or XML execution plan:
SELECT
*
FROM
dbo
.
SalesOrderDetail
WHERE
SalesOrderID
=
43670
AND
SalesOrderDetailID
>
112
Listing 4-20.
This query could benefit from an index on the
SalesOrderID
and
SalesOrder-
DetailID
columns, but no missing indexes information is shown this time. One
limitation of the Missing Indexes feature which this example has revealed is that it
does not work with a trivial plan optimization. You can verify that this is a trivial plan
by looking at the graphical plan properties, shown as Optimization Level
TRIVIAL
, or
by looking at the XML plan, where the
StatementOptmLevel
is shown as
TRIVIAL
.
You can avoid the trivial plan optimization in several ways, as I'll explain in
Chapter 5
,
The Optimization Process
(for now, you'll just have to take it on faith). In our case, we're
just going to create a non-related index by running the following statement:
CREATE
INDEX
IX_ProductID
ON
dbo
.
SalesOrderDetail
(
ProductID
)
Listing 4-21.
What is significant about this is that, although the index created will not be used by our
previous query, the query no longer qualifies for a trivial plan. Run the query again, and
this time the XML plan will contain the following entry:
<
MissingIndexes
>
<
MissingIndexGroup
Impact
=
"
99.7137
"
>
<
MissingIndex
Database
=
"
[AdventureWorks]
"
Schema
=
"
[dbo]
"
Table
=
"
[SalesOrderDetail]
"
>
<
ColumnGroup
Usage
=
"
EQUALITY
"
>
<
Column
Name
=
"
[SalesOrderID]
"
ColumnId
=
"
1
"
/>
</
ColumnGroup
>
