Jeff Moden (1/13/2010)
Good article and makes a lot of sense... providing that the table doesn't have a lot of inserts where every index counts AGAINST inserts. Most people don't know that INSERTS on heavily indexed tables are one of the primary causes of very high reads. For example and using the given example table, if I run the following code...... then we can see that a single insert after the index is created now takes 11 reads instead of just 3.
On my 2008 system, the logical reads change from 3 to 5 (there are 7090 rows).
Jeff Moden (1/13/2010)
If you have an IO bound system, you need to be really careful about adding any indexes to tables that have a high insertion rate. Like everything else, "It Depends" and only a bit of "complete" testing will show you things that you may have not considered.
It is important to consider the impact of index maintenance, for sure, but there's nothing unusual about that. Every new index has to justify itself in terms of the trade off between increased maintenance overhead versus the benefits produced by having the index. Generally, one would expect most useful indexes to involve a net saving in reads; the reduced reads coming from queries that benefit from the index should outweigh the reads added by index maintenance.
Don't get me started on what a poor metric the number of 'logical reads is' 😉
All that aside, I found this to be a good article, and a fine approach for any system that finds itself requiring a lot of 'ends with' searches. It is also well presented and clear - so top marks for style.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi