Chapter 12. Reading query execution plans
When SQL Server is given a query to execute, it runs the query through an internal query optimizer. The optimizer looks at what the query is trying to do, what indexes are available to help, what condition those indexes are in, what load the server is currently handling, literally dozens of different factors. It considers all the ways it might execute the query—scanning a table, seeking in an index, and so on. There’s a limit on how long it’ll run through the permutations, because eventually it’ll be wasting time instead of running the query! At some point it’ll pick the best choice found by then, and that becomes the execution plan for that query. SQL Server then runs the plan, executing the query.
Imagine that you need to drive to work. You probably have several different routes that you can use, and you might take into account several factors:
- How much fuel do you have in the car? If you’re getting low, you might discard a longer or a more stop-and-start routing.
- What condition are the roads in? Watching the morning news or checking an online traffic report might make one route more attractive than another.
- Do you have any additional stops along the way? Those errands might dictate specific routes.