Further Explorations in Plan-Cache Metadata

SQL Server 2005 provides metadata that gives you information about cached query plans in XML format, so that you can view plans without having to run them yourself or compare multiple plans at once. “Examining XML Plans in Cache,” January 2008, InstantDoc ID 97562 introduced you to working with plan-cache metadata using the sys.dm_ exec_cached_plans dynamic management view. We’ll continue the discussion by looking at several additional metadata objects that you can use to obtain further information about query plans in your plan cache.

More Metadata

“Examining XML Plans in Cache” presented a simple query that used the new SQL Server 2005 CROSS APPLY operator to combine sys.dm_exec_cached_ plans with two functions, sys.dm_exec_query_plan and sys.dm_exec_sql_text, which Microsoft introduced in SQL Server 2005 SP2. Combining these three objects lets you see reuse information about every cached plan as well as the text of the query and the XML form of the query plan.

The sys.dm_exec_query_plan function returns a query plan in an XML-type output column. Sys .dm_exec_text_query_plan is similar to sys.dm_exec_query_plan and can be used in place of sys.dm_exec_ query_plan. However, the two functions aren’t quite interchangeable because sys.dm_exec_text_query_plan requires two additional parameters specifying the start and end positions in the batch where the particular statement you’re interested in can be found. If you use the default values for start and end (actually statement_ start_offset and statement_end_offset), you can get the plan for the entire batch.

There are some other differences between sys.dm_ exec_query_plan and sys.dm_exec_text_query_plan, as described in SQL Server Books Online (BOL):

BOLgoes on to say the following about sys.dm_exec_ text_query_plan (this information isn’t quite correct, though): “When an ad hoc query uses simple or forced parameterization, the query_plan column will contain only the statement text and not the actual query plan. To return the query plan, call sys.dm_exec_text_query_ plan for the plan handle of the prepared parameterized query.”

I discussed the use of such plans in “Examining XML Plans in Cache” and referred to these statementtext– only plans as truncated plans representing what I call shell queries. I also mentioned that plans that have been parameterized aren’t the only ones that will have a truncated plan. Ad hoc plans for unsafe plans will also show only the statement, and in such instances, no prepared plan is available that would enable you to see the entire plan.

Viewing Autoparameterized Queries in Cache

Let’s look at an example of how sys.dm_exec_text_ query_plan and sys.dm_exec_query_plan display such truncated plans. We’ll run two of the same queries I used in “Examining XML Plans in Cache”—but this time using the sys.dm_exec_text_query_plan and sys .dm_exec_query_plan functions. Because my batch will have only one statement, I can use the default parameters for the start and end positions in the batch. Run the code in Listing 1 to clear the plan cache and to run two queries that can be autoparameterized to use the same plan. Listing 1 then displays the contents of plan cache and for each plan includes an XML representation using both the nvarchar data type and the xml data type. Note that the XML data type column is a link that you can click to see the entire XML document.

If no other concurrent activity is occurring on your SQL Server system, you should get three rows returned from the metadata query. The two rows with an objtype value of adhoc will be the shell queries and contain only the SQL statement itself, not the entire execution plan. Only the row for the prepared query will have the entire plan available in the XML.

When you examine the output of the column called text_query_plan, the plan might look incomplete. Examining it in the output from Listing 1 doesn’t show the appropriate terminator. However, you should be able to display the entire XML document by copying the single value in the text_query_plan column for the Prepared query row and pasting it in another query window.

So where do you get statement_start and statement_ end information if you’re interested in only a single statement in a multi-statement batch? Two other metadata objects can help you obtain this information.

Sys.dm_exec_requests

The sys.dm_exec_requests dynamic management view returns one row for every currently executing request within your SQL Server instance. Sys.dm_exec_requests is useful for many purposes besides tracking down plan-cache information. This dynamic management view contains the sql_handle and plan_handle for the current statement as well as resource-usage information for each request.

For troubleshooting purposes, you can use the sys .dm_exec_requests view to help identify long-running queries. This view contains information about each batch that was running and also contains the columns statement_start_offset and statement_end_offset, which indicate the position within the entire batch of the currently executing statement. The offsets start at 0; an offset of -1 indicates the end of the batch. You can use the statement start and end values as the second and third parameters passed to sys .dm_exec_text_query_plan to extract the plan for only the currently executing statements, as the code in Listing 2 shows.

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.