Analyzing execution plans

Primarily, we’ll interact with the query processor through execution plans, which, as mentioned earlier, are ultimately trees consisting of several physical operators that, in turn, contain the algorithms to produce the required results from the database. Given that we will make extensive use of execution plans throughout this book, in this section, we will learn how to display and read them.

You can request either an actual or an estimated execution plan for a given query, and either of these two types can be displayed as a graphic, text, or XML plan. Any of these three formats show the same execution plan – the only difference is how they are displayed and the level of detail they contain.

When an estimated plan is requested, the query is not executed; the plan that’s displayed is simply the plan that SQL Server would most probably use if the query were executed, bearing in mind that a recompile, which we’ll discuss later...