I have always thought that an index seen was preferable to an index scan. It seems like the general rule that so many DBAs and developers follow, looking to convert every scan in an execution plan to a seek. Often that results in better performance, and I've seen many people resort to using hints to enforce this behavior in SQL Server when the query optimizer (QO) or Query Processor (QP) fails to choose their indexes.

This past week Rob Farley wrote a great blog post that taught me something about seeks, scans, and the fact that one is not always better than the other. It has a great title and is worth a few minutes of your time to read: Covering, schmuvvering – when a covering index is actually rubbish. In the post Rob shows that a seek can be worse than a scan in some cases, in his example due to a Residual Predicate.

I have seen so many people mistrust the query processor in SQL Server over the years, often resorting to hints when it seemed that the best index wasn't being chosen. I've felt like doing that before as well, spending afternoons cursing the developers at Microsoft that their product wouldn't choose an index that I knew was a better choice.

Over the years I've talked with the people that build the code behind the query optimizer and often it seems someone is submitting a bug in the way the QO/QP works. Most of the time, however, I find my respect growing for that team, and often find that the individual is falling victim to the "it works on my machine" syndrome. Too often someone is observing a single case, a single data set, and limited concurrency, all of which can drastically change the performance of a query on your system when they grow.

SQL Server doesn't have a perfect QP/QO system, but it has a very, very good one. Using too many hints almost feels like hard-coding a value in the system. There are times that it makes sense, but they are very rare.

This post also reminds me that there are so many things to learn about SQL Server, and gaining a deeper understanding of how the internals of SQL Server work can pay off with much more efficient, and scalable code that handles your load as it grows.

Steve Jones

PS - This post makes me want to see Rob's pre-conference session this October at the PASS Summit. Hopefully he will get picked and many of us will get the chance to learn more nuggets like this one.