a. Find one or more of the following articles:

• The Microsoft Technet article, "Advanced Query Tuning Concepts"

• The madeira.co.il article "LOOP, HASH, and MERGE Join Types"

• The article by Randy Dyess, "Optimizer Join Methods"

Pick ONE of the three join methods, and state:

1. What the join method is and how it works

2. Under what circumstances will that join method probably be used (25)

b. Describe the following:

1. Under what circumstances will the SQL Server query optimizer choose to use a nonclustered index plus a key lookup?

2. If the query plan in 1 is cached, under what circumstances will the performance of that query be very poor, and why? (15)

c. Given the mantra, "Fewer page reads means better performance," describe how an index can improve performance. Refer to one or more figures in the textbook if that helps your description.