Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

VII.1.1. Understanding How an Optimizer Works

Whenever you present a request to SQL Server, it must choose one of many possible approaches to give you your requested results. These strategies often employ very different processing steps; however, each strategy must achieve the same results.

A good real-world analogy is beginning a trip by car. If you need to drive from your house to the one across the street, you don't need much trip planning: You really have only one way to get there. However, what if you want to visit three other friends, each of whom lives in a different city, all of which are spread hundreds of kilometers apart? When planning your route, it doesn't matter which friend you visit first, but you must somehow see them all. Dozens of streets and roads interlace these cities. Some are high-speed freeways; others are meandering country lanes. Unfortunately, you have very limited time to visit all these friends, and you've heard rumors that traffic and construction are very bad on some of these roads.

If you were to sit down and map all the potential routes from the thousands of possible combinations, it's likely that it would take you longer to complete your mapping than if you simply picked one friend to visit at random, followed by another, and so on. Alas, you do need some sort of plan. It makes sense to get the most recent maps and construction reports and then do your best to chart an optimal course that visits all three friends as quickly as possible. If you ...