Listing 2 includes a batch that executes three statements.
Your output should contain two result sets.
The first will be the output of the SELECT from the
Sales.SalesOrderHeader table, and the second returns
a row showing the contents of sys.dm_exec_requests
for the currently running batch, with the row’s last
column containing the plan for only the statement
that returns the CROSS APPLY query. (That is,
you won’t see the plan for the SELECT from Sales
.SalesOrderHeader because that statement isn't currently
running when the CROSS APPLY query
is run.) Note that including the asterisk (*) in the
SELECT list indicates that this query should return
all the columns from sys.dm_exec_requests view (as
well as from sys.dm_exec_text_query_plan). You
can replace the asterisk with the columns that you’re
particularly interested in, such as start_time or
blocking_session_id.
You can use sys.dm_exec_requests and sys.dm_
exec_text_query_plan to track down slow-running
queries on your production system. Keep in mind
that the sys.dm_exec_sql_text function doesn’t let you
specify the start and end offsets, so by default you’ll
see the SQL text for the entire batch. However, you
can use the start and end offsets along with the substring
function to manually extract the SQL for the
current statement only. The code in Listing 3, page 36,
shows you the SQL text and query plan for the three
currently executing statements that have the longest elapsed times. The SUBSTRING function needs to
divide the offset values by two, because SUBSTRING
takes the character-position values as parameters,
but the values returned in statement_start_offset and
statement_end_offset are the offsets in bytes in string
of type nvarchar. Nvarchar requires two bytes per
character.
Note that the sys.dm_exec_requests view only
shows you what’s currently running. If you want to
see metadata for prior queries, however, you can use
another view: sys.dm_exec_query_stats.
Using sys.dm_exec_query_stats
For detailed troubleshooting, you can use sys.dm_
exec_query_stats to return performance information
for individual queries. This view returns performance
statistics for cached query plans, aggregated across all
executions of the same query. The view contains one
row per query statement within each cached plan, and
the lifetime of the rows in the view is tied to the plan
itself. You’ll see information only for plans that are currently
still in cache. After a plan is removed from the
cache, sys.dm_exec_query_stats will no longer display
any information about the plan.
The sys.dm_exec_query_stats view returns both a
sql_handle and a plan_handle as well as the start and
end offsets like those you saw in sys.dm_exec_requests.
The query in Listing 4 uses sys.dm_exec_query_stats
to return the top ten queries by total CPU time. Using
this information, you can identify the most expensive
queries that have been run on your SQL Server system
and have plans that are still cached.
Sys.dm_exec_query_stats returns a large amount of
performance information for each query, including the
number of times the query was executed and the cumulative
I/O, CPU, and duration information. Note that
this view is updated only when a query is completed,
so you might need to retrieve information multiple
times if your server currently has a large workload. As
in Listing 2, you can replace the asterisk by the list of
columns that you’re particularly interested in.
Metadata Everywhere
We’ve delved further into the metadata that keeps
track of and displays information about your queries
and their execution plans. I showed you the views and
functions available and how they could be combined
with each other to give different sets of information. If
you explore the metadata in more depth, experimenting
with all the information provided in the columns contained
in the output, you’ll be able to determine which
views work best in your environment and can help you
find the performance data most useful to your troubleshooting
endeavors.
End of Article
Prev. page
1
[2]
next page -->
