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):

• With sys.dm_exec_text_query_plan, the query plan’s output is returned in text format, rather than XML. Although the returned plan looks like an XML plan, it’s actually contained in a column of type nvarchar(max).
• With sys.dm_exec_text_query_plan, the query plan’s output isn’t limited in size, as the XML output of sys.dm_exec_query_plan is.
• The sys.dm_exec_text_query_plan function lets you specify individual statements within the batch. As I mentioned, this is possible when using nondefault values for statement_start_offset and statement_ end_offset, for batches that contain multiple statements. I’ll provide an example using these parameters later in this article.

BOL goes 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.

Continued on page 2