Databases Reference
In-Depth Information
|--Hash Match(Aggregate, HASH:([Person].[Address].[City]), RESIDUAL …
|--Index Scan(OBJECT:([AdventureWorks].[Person].[Address]. [IX_Address …
Listing 1-5.
Finally, be aware that there are still other ways to display an execution plan, such as using
SQL trace (for example by using SQL Server Profiler) or the
sys.dm_exec_query_plan
dynamic management function (DMF). As mentioned earlier, when a query is optimized,
its execution plan may be stored in the plan cache, and the
sys.dm_exec_query_plan
DMF can display such cached plans, as well as any plan which is currently executing.
The following query in Listing 1-6 will show the execution plans for all the queries
currently running in the system. The
sys.dm_exec_requests
dynamic management
view (DMV), which returns information about each request currently executing, is used
to obtain the
plan_handle
value, which is needed to find the execution plan using the
sys.dm_exec_query_plan
DMF. A
plan_handle
is a hash value which represents a
specific execution plan, and it is guaranteed to be unique in the system.
SELECT
query_plan
FROM
sys
.
dm_exec_requests
CROSS
APPLY
sys
.
dm_exec_query_plan
(
plan_handle
)
WHERE
session_id
=
135
Listing 1-6.
The output will be a result set containing links similar to the one shown in Listing 1-3
and, as explained before, clicking the link will show you the graphical execution plan. For
more information about the
sys.dm_exec_requests
DMV and the
sys.dm_exec_
query_plan
DMF, you should go to Books Online.
If you're not yet familiar with execution plans in all their myriad forms, this section
should have given you enough background to follow along through the rest of the topic.
We'll cover more topics and skills as we go along, but, in the meantime, let's take a look at
one of the most fundamental puzzles of query optimization.
