There is something disconcerting in the idea of the Query Optimizer. For a programmer, the desire to define not only what the result of a process should be, but also how that process should take place, is pretty ingrained. Many find deeply unsettling the idea that the Query Optimiser merely uses your SQL code to try to understand what data you need, and then decides for itself on the most efficient plan to retrieve that data.

The optimizer is a highly-impressive and complex piece of software, capable of evaluating tens of thousands of different possible join orders, physical operators, data access paths and methods, and so on, in the blink of an eye, and coming up with a highly efficient plan, in most cases. For many it is a black box and they would rather that it remained so.

However, a great deal has changed in the last couple of years to demystify the whole subject of execution plans, the physical 'showplan' operators, query statistics, and query compilation.  We now have a group of people such as Grant Fritchey, Gail Shaw, Fabiano Amorim, Craig Freedman, Holger Schmeling, Paul White and Benjamin Nevarez, who are writing about these topics in an approachable way. For anyone involved with databases, it is worth reading their work as it helps to understand and give context to a number of 'best practices' that might otherwise seem like arbitrary decrees.

Beyond the obvious cautions regarding use of ad-hoc queries, or letting statistics become out-of-date, the overall message for the developer is clear: let the optimizer drive. The developer can, and should, concentrate on creating efficient and effective processes, write straightforward queries that are sympathetic to the database's indexing strategy, and allow the Query Optimiser to create the most effective plan.

When a stored procedure turns out to be a slow performer in development, for example, it's tempting, having stared at the execution plan, to decide that the optimizer got it wrong, and to try to 'hint' your way out of trouble, or alternatively to throw more single-use indexes at the problem. Instead, it's better to back off a bit and see if the algorithm itself is inefficient, the process is long-winded. In other words, give the Query Optimiser the space it needs to do its job well.

Cheers,

Tony.