Performance Tuning - Pro SQL Azure - page 246

This statement returns 25 rows. To view the execution plan, you need to request it before running
the statement. Either press Ctrl + M or choose Query Include Actual Execution Plan from the menu in

SQL Server Management Studio, and re-run the SQL statement. You should now see an Execution Plan
tab. Click the tab to see output similar to that shown in Figure 12-2.

Figure 12-2. Sample execution plan, not tuned
In order to fully understand the previous execution plan, let's review additional symbols often seen
in execution plans. Table 12-5 shows a few symbols that have clear performance implications.

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

Operator 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 Azure 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 and the execution plan in Figure 12-2
shows two index scans, 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;