Database Reference
In-Depth Information
■
remember to switch Query ➤ Show execution plan off in management Studio, or you will see the graphical,
rather than textual, execution plan.
Tip
Plan Cache
One final place to access execution plans is to read them directly from the memory space where they are stored,
the plan cache. Dynamic management views and functions are provided from SQL Server to access this data.
To see a listing of execution plans in cache, run the following query:
SELECT p.query_plan,
t.text
FROM sys.dm_exec_cached_plans r
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) t;
The query returns a list of XML execution plan links. Opening any of them will show the execution plan. These
execution plans are the compiled plans, but they contain no execution metrics. Working further with columns
available through the dynamic management views will allow you to search for specific procedures or execution plans.
While not having the runtime data is somewhat limiting, having access to execution plans, even as the query is
executing, is an invaluable resource for someone working on performance tuning. As mentioned earlier, you might
not be able to execute a query in a production environment, so getting any plan at all is useful.
Query Resource Cost
Even though the execution plan for a query provides a detailed processing strategy and the estimated relative costs
of the individual steps involved, it doesn't provide the actual cost of the query in terms of CPU usage, reads/writes
to disk, or query duration. While optimizing a query, you may add an index to reduce the relative cost of a step.
This may adversely affect a dependent step in the execution plan, or sometimes it may even modify the execution
plan itself. Thus, if you look only at the execution plan, you can't be sure that your query optimization benefits the
query as a whole, as opposed to that one step in the execution plan. You can analyze the overall cost of a query in
different ways.
You should monitor the overall cost of a query while optimizing it. As explained previously, you can use Extended
Events to monitor the
duration
,
cpu
,
reads
and
writes
information for the query. Extended Events is an extremely
efficient mechanism for gathering metrics. You should plan on taking advantage of this fact and use this mechanism
to gather your query performance metrics. Just understand that collecting this information leads to large amounts of
data that you will have to find a place to maintain within your system.
There are other ways to collect performance data that are more immediate than Extended Events.
Client Statistics
Client statistics capture execution information from the perspective of your machine as a client of the server. This
means that any times recorded include the time it takes to transfer data across the network, not merely the time
involved on the SQL Server machine itself. To use them, simply click Query
➤
Include Client Statistics. Now, each
time you run a query, a limited set of data is collected including the execution time, the number of rows affected,
the round-trips to the server, and more. Further, each execution of the query is displayed separately on the Client