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.
|2 Column Index||5||91% Decrease|
|Single Column Index||8||85% decrease|
Lesson Learned: Create indexes on frequent JOIN patterns and WHERE criteria.