I have a customer table with 3,000 rows. Here are some stat runs with various indexes. You have to turn on your statistics first

set statistics io on
set statistics time on

First I executed a query with no index.

select fname,lname from customer
where fname = 'Hasad' and lname = 'Cook'

Stats: Table ‘customer’. Scan count 1, logical reads 56

Round 2. Create a NONCLUSTERED index on lname and fname

create NONCLUSTERED INDEX IX_customer_fname_lname on customer (lname, fname);

Run the select again.

Stats: Table ‘customer’. Scan count 1, logical reads 5

Looking at the Execution plan we also see that instead of a Index Scan on the PK we get a KeyLookup and an Index Seek on our new NONCLUSTERED Index. We also notice a 91% decrease in the amount of logical reads.

So I then dropped the index and tried creating indexes only on lname and then only on fname.

drop index IX_customer_fname_lname on customer
create NONCLUSTERED INDEX IX_customer_lname on customer (lname);
--run the select
drop index IX_customer_lname on customer;
create NONCLUSTERED INDEX IX_customer_fname on customer (fname);
--run the select

Stats: Table ‘customer’. Scan count 1, logical reads 8.

Here we notice that our new index does not cover by conditions in the where clause and we notice a difference in the number of logical reads. The read count compared to no index shows us an 85% decrease in the number of reads.

One interesting note is the difference between the index that covers both where conditions versus the one that covers one where condition. We see a 37% increase in reads (8 reads on the single column index, 5 reads on the 2 column index). That 37% does not make a big difference in my little 3,000 row table, but that could be huge on a large data set.

Index LogicalReads PctChange
No Index 56
2 Column Index 5 91% Decrease
Single Column Index 8 85% decrease

Lesson Learned: Create indexes on frequent JOIN patterns and WHERE criteria.