Performance Tuning - Pro SQL Azure - page 250

To remove the index scan on TestUserType, you add another index following the previous
guidelines. Because no columns are needed in the SELECT clause, you can leave the INCLUDE section
alone. Here's the index:
CREATE INDEX idx_testusertype_001 ON TestUserType
(UserTypeKey, UserType)

■

Note Although it's minimal, there is a risk associated with adding new indexes in production systems. Certain
routines, especially batch programs, typically depend on data being properly ordered to calculate running sums or
carry out specific tasks. If an index is favored over another to run a query, it's possible for the new index to be
used, which has the potential to change the order in which the data is returned. If all your statements include an
ORDER BY clause, this problem won't affect you. But if some of your programs rely on the natural order of records,
beware!

Your execution plan should now look like that in Figure 12-6. Notice that the physical operator has
been changed to a loop. Also notice that the cost of the query has shifted away from the JOIN operator:
the highest relative cost (76%) of the plan is spent reading the data from the TestUserType index.

Figure 12-6. Effect of indexing on the physical operators

But the tuning exercise isn't over just yet. If you hover your cursor on the TestUserType_001 index,
you see that the loop performed a lookup (index seek) on that index 50 times (look for Number of
Executions in Figure 12-7)! This isn't great, but it's probably better than without the index, because SQL
Azure picked this new execution plan.
To reduce the number of lookups, you can change the order of the fields by creating a new index.
Let's run this statement:
CREATE INDEX idx_testusers_002 ON TestUsers
(UserType, AgeGroup) INCLUDE (Name)