Databases Reference
In-Depth Information
How it works...
Before we move further, let us clarify that scan is not always bad and seek is not always good,
but in most cases, especially while working with small dataset from big table, seek is going to
be the preferred way. Also, it is not always possible to remove scan in each and every query. If
there is any performance issue occurring in the query and if the query is making scan, which
is consuming more resource, then it will be better to remove scan, if possible, otherwise look
for another alternative. Suppose your table has 10 million rows out of which you are retrieving
only 100 rows, then you would use seek. But from the same table, for any reason, if you are
returning 9.5 million rows, then it is better to have scan rather than seek.
In step 1, when we have executed query with join, there were no indexes defined on both
tables and so table scan was the only option for optimizer to go for.
In step 3, we have created clustered index on
SalesOrdHeaderDemo
table and executed the
same
SELECT
query that we have used in step 1, but Execution plan was giving Clustered
index scan on first table as against table scan. Clustered index seek is desired, but we don't
have any predicate on the first table so it is not possible for the index to seek for any particular
record, so it scans the complete index.
In step 6, we had created clustered index on second table with non-key fields,
SalesOrderID
and
SalesOrderDetailID
, out of which
SalesOrderID
is used as a predicate in the
ON
clause to compare records with a parent table which resulted in clustered index seek.
There's more...
For more details about different types of indexes, refer to
Chapter 9
,
Implementing Index
and
Chapter 10
,
Maintaining Index
.
As this chapter discusses the execution plan, we have not covered details about indexes here.
Introducing Key Lookups, finding them in
execution plans, and resolving them
Key Lookup is a bookmark lookup on a table with a clustered index. Key Lookup is used
by SQL Server while retrieving information regarding non-key column. All the queries that
use non-clustered index wouldn't have Key Lookup but all Key Lookup occurrences are
accompanied by a non-clustered index. One more thing to remember is that Key Lookup
always enjoys the company of Nested Loop operator.
Search WWH ::
Custom Search