When your query must always return in a certain amount of time, trusting the optimizer might not be the best choice. Suppose your select must return in one second no matter what, and it usually returns in 50 ms, but sometimes, once in 100K calls, it takes full five seconds to complete, and clearly such query does not meet the requirement to return in one second. Sounds familiar? Sure, optimizers do behave like this from time to time. It is very tempting to use hints or even plan freezing. However, neither hints nor plan freezing are long term maintenance-free solutions, and the reason is simple: what today looks like a great plan might at some later time become counterproductive.
In cases like this
index covering really shines
Scanning a range of a covering index is a very simple and efficient plan. Even better, it is one and the same plan for all possible combinations of parameters, so you may not expect unpleasant surprises from a confused optimizer. Of course, if the amount of data increases, the response time may increase too, but you may monitor it and react accordingly if needed. If you do not load huge amounts of data, your response time will not jump from 50 ms to five seconds any time soon, as long as the optimizer chooses the same range scan over and over again.
Responding to the
recent post by SQL server MVP Tibor Karaszi
, I think that not all queries are born equal - some must respond in a certain time even if that slows down other activity on the server. This means that in some cases it is OK if index covering slows down your modifications, provided that the modifications have lower priority.