Databases Reference
In-Depth Information
An additional predicate on the
SalesOrderID
column is evaluated like any other scan
predicate, as listed in:
abs([AdventureWorks].[Sales].[SalesOrderDetail]. [SalesOrderID])=[@2]
Listing 4-7.
So, in summary this shows that, as we expected, SQL Server was able to perform a seek
operation on the
ProductID
column but, because of the use of the ABS function, was
not able to do the same for
SalesOrderID
. The index was used to navigate directly to
find the rows that satisfy the first predicate, but then had to continue scanning to validate
the second predicate.
The Database Engine Tuning Advisor
Currently, all major commercial database vendors include a physical database design tool
to help with the creation of indexes. However, when these tools were first developed,
there were just two main architectural approaches considered for how the tools should
recommend indexes. The first approach was to build a stand-alone tool with its own
cost model and design rules. The second approach was to build a tool that could use the
Query Optimizer cost model.
A problem with building a stand-alone tool is the requirement for duplicating the cost
module. On top of that, having a tool with its own cost model, even if it's better than the
optimizer's cost model, may not be a good idea because the optimizer still chooses its plan
based on
its own
model.
The second approach, using the Query Optimizer to help on physical database design,
was proposed in the database research community as far as back as 1988. Since it's the
optimizer which chooses the indexes for an execution plan, it makes sense to use the
