Performance Tuning - Pro SQL Database for Windows Azure - page 223

Table 11-5. Lookup, index scan, and index seek operators

Operator SSMS Symbols Comment

Lookup Lookups can be costly when the statement returns thousands of rows

and a lookup is needed for every row. If you determine that the lookup is
costly, consider creating a covering index.

Index scan An index or clustered index scan may or may not be a bad thing.

Scanning means that SQL Database reads all the records sequentially in
the index. Scanning isn't good for performance if you return a fraction of
the records being scanned, in which case an index is needed. But if you
want to return all the records from a table, a scan is necessary.

Index seek An index or clustered index seek means the first record matching the

query is found quickly, without scanning.

Execution plans can show many other symbols that have very specific meanings, including hints that a query is
using multiple processors, and so on.
Because no indexes are defined on the underlying tables (outside of the required clustered index) and the
execution plan in Figure 11-2 shows two clustered index scans (which are equivalent to table scans in this case),
you have a potential performance problem. The INNER JOIN logical operator is executed with a hash match physical
operator. You can see a thicker line coming into the hash match; hovering your cursor on this line shows you that
50 records are being consumed by the JOIN operation (see Figure 11-3 ). Also, you can see that a clustered index scan
is being used to fetch data for both tables. Finally, note that the hash match operation consumes 73% of resources of
the entire statement; this means it takes more time to JOIN the records than to read the data.

Figure 11-3. Verifying how many records are being consumed by a JOIN operator

In summary, the statement you've been working with has three potential issues:

• Index scans. An index scan is performed on both tables, likely causing more reads
than necessary.

• Heavy operator processing. The hash match operation is consuming most of the
processing time.