Databases Reference
In-Depth Information
However, when an actual plan is requested, the query needs to be executed, and the plan
is then displayed along with the query results. On the other hand, when an estimated
plan is requested, the query is naturally not executed; the plan 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, may generate a different plan at
execution time). Nevertheless, using an estimated plan has several benefits, including
displaying a plan for a long-running query for inspection without actually running the
query, or displaying a plan for update operations without changing the database.
You can display the graphical plans in SQL Server Management Studio by clicking the
Display Estimated Execution Plan
or
Include Actual Execution Plan
buttons from
the SQL Editor toolbar, which is enabled by default. Clicking on
Display Estimated
Execution Plan
will show the plan immediately, without executing the query whereas,
to request an actual execution plan, you need to click on
Include Actual Execution Plan
and then execute the query.
As an example, copy the following query to the Management Studio Query Editor, select
the
AdventureWorks
database, click the
Include Actual Execution Plan
button, and
execute the query.
SELECT
DISTINCT
(
City
)
FROM
Person
.
Address
Listing 1-1.
This displays the plan shown in Figure 1-2.
Figure 1-2:
Graphical execution plan.