Analyzing the SQL Diagram begins by looking for the smallest, underlined number. In our case it
is 0.002 next to the CLASS table. To limit the number of rows the query needs to process,
starting here will trim our result sets the soonest. In other words, if we can make SQL Server
start with this table the query can be executed optimally. I will leave it to Dan Tow to prove this
fact.
We know that we want SQL Server to hit the CLASS table first, but how do we do that? The
easiest way is to ensure an index exists on our criteria. In this case that means the NAME
column of the CLASS tables. In our case, an index does not currently exist, so let’s create it and
see what our new execution plan is:
create index cl_name on class(name)
The query executed in 20 seconds (which is a 90% improvement from 200 seconds) and the plan
has changed. The CL_NAME index is now used to find the two rows in the CLASS table with a
NAME=’SQL TUNING’, which is far better than performing an Index Scan. We achieved partial
results but an Index Scan is still done against the REGISTRATION table. Why would that be?
When we review the indexes that exist on that REGISTRATION we find one index named
REG_PK that contains the STUDENT_ID and CLASS_ID columns in that order. Since our query
is hitting the CLASS table first it cannot use this index that starts with STUDENT_ID so it must
perform a full Index Scan. This index could be modified to switch the columns around, but that
could affect many other queries and may require significant testing. In this case, and since I
know I need an index with a leading edge of CLASS_ID, I created an index on just that column:
