Databases Reference
In-Depth Information
<
ColumnGroup
Usage
=
"
INEQUALITY
"
>
<
Column
Name
=
"
[SalesOrderDetailID]
"
ColumnId
=
"
2
"
/>
</
ColumnGroup
>
</
MissingIndex
>
</
MissingIndexGroup
>
</
MissingIndexes
>
Listing 4-22.
The
MissingIndexes
entry in the XML plan can show up to three groups:
equality
,
inequality
, and
included
; and the first two are shown in this example using the
ColumnGroup
attribute. The information contained in these groups can be used to
create the missing index; the key of the index can be built by using the equality columns,
followed by the inequality columns, and the included columns can be added using the
INCLUDE
clause of the
CREATE
INDEX
statement. SQL Server 2008 Management Studio
can build the
CREATE
INDEX
statement for you and, in fact, if you look at the graphical
plan, you can see a Missing Index warning at the top, including a
CREATE
INDEX
command, as shown in Figure 4-7:
Figure 4-7:
Plan with a Missing Index warning.
Notice the impact value of 99.7137 -
Impact
is a number between 0 and 100 which gives
you an estimate of the average percentage benefit that the query could obtain if the
proposed index were available.
You can right-click on the graphical plan and select
Missing Index Details
to see the
CREATE
INDEX
command that can be used to create this desired index, as shown
in Listing 4-23.