SQL Server Magazine

As you learned last month in “Managing Plan-Cache Size,” InstantDoc ID 97373, SQL Server 2005 provides some controls to keep plan cache from getting too large. In addition to the techniques I discussed that you can use to help control plan-cache size, you can also use metadata that’s available in SQL Server 2005 to see the actual plans stored in the plan cache. There are about a dozen different dynamic management views and functions that offer information about plan-cache contents, and those don’t include the metadata that provides information about memory usage by plan cache. The core dynamic management view that gives information about plan-cache contents is called sys.dm_exec_cached_plans, and we’ll examine that view in detail and how you can use it to view an XML plan in plan cache. By viewing cache metadata, you can see any query plan that’s in cache without having to run the query yourself, and you can also compare many plans at the same time.

Using sys.dm_exec_cached_plans
In SQL Server 2000, the only information available about the contents of plan cache is through a pseudo-table called syscacheobjects. This view is still available in SQL Server 2005 as the sys.syscacheobjects view. You can look at SQL Server 2005’s definition of sys .syscacheobjects by using the OBJECT_DEFINITION function, and you’ll see that the basis of this view is the dynamic management view sys.dm_exec_cached_plans. Here’s the code to do this:

SELECT object_definition (object_id (‘sys.syscacheobjects’))

(Some code in this article wraps to multiple lines to fit on the printed page.) Observe the object definition returned and notice that the FROM clause contains a derived table that uses an OUTER APPLY operator to combine sys .dm_exec_cached_plans with the sys.dm_exec_plan_ attributes function. Think of the OUTER APPLY operator as if it’s joining a view or table with a tablevalued function and applying the function to each row in the table or view. This OUTER APPLY allows sys .syscacheobjects to display many attributes for each plan, including the set_options bit string and the dbid, language_id, and date_format values. Another OUTER APPLY is then used to “join” this derived table with the sys.dm_exec_sql_text function, using the plan_handle as a parameter, to retrieve the text of the query that generated each of the plans.

The richness of the content of sys.dm_exec_cached_ plans lets you pick and choose exactly what plan-cache information you want to see. I’ll use the sys.dm_exec_ cached_plans view and combine it with the sys.dm_exec_sql_text function as sys.syscacheobjects does. Additionally, I’ll combine the sys.dm_exec_cached_plans view with the sys.dm_exec_query_plan function, which returns, among a few other columns, the corresponding query plan in XML format. I discuss XML query plans in “The XML Query-Plan Treasure Trove,” May 2007, InstantDoc ID 95498, but there I focus on the plans retrieved using SET SHOWPLAN_XML ON and SET STATISTICS XML ON. Those options are fine when you want to examine only one plan at a time, but when you want to examine one particular plan among many that have already been cached, you need to be able to examine plan cache directly.

The code in Listing 1 returns all the compiled plans from your plan cache except for those that are examining the dynamic management objects. In other words, I’m not interested in seeing the plans for the queries I’m using to see the plans.

In “Controlling Parameterization,” August 2007, InstantDoc ID 96349, I examined sys.syscacheobjects to see the effect of autoparameterization and looked only at plans of type prepared. However, compiled plans come in three varieties: ad hoc, prepared, and proc. I discussed these three types of plans back in September 1999 in “Inside SQL Server: SQL Server 7.0 Plan Caching,” InstantDoc ID 5915, which focused on SQL Server 7.0, but almost everything I said in that article about the differences between the three types of plans is still valid in SQL Server 2005 and 2000. In SQL Server 2005, a plan that’s autoparameterized by SQL Server will actually have two rows in the plan cache, both a row for an ad hoc plan and one for a prepared plan. However, the rows for the ad hoc plans are considered shell queries and are cached only to make it easier to find the autoparameterized version of the query if the exact same query with the same constant is reused later.

Viewing an XML Plan in Plan Cache
To see an example of the two types of plans cached for autoparameterized queries, run the code in Listing 2. This code first clears the plan cache, then runs two similar queries, which vary only by the constant used in the WHERE clause. Because there is a unique index on the column in the WHERE clause, the plan is considered safe (i.e., there’s only one possible plan) and is autoparameterized. Running the code in Listing 1 should now give you output with the first four columns similar to that which Table 1 shows.

You should notice two individual queries with distinct constants, which are cached as ad hoc queries. These are only the shell queries mentioned earlier. These shell queries don’t contain the full execution plan but only a pointer to the full plan in the corresponding prepared plan. The third row in Table 1 has an objtype of prepared. The full query plan is associated with the prepared plan, and you can observe that the plan was used twice. In addition, the text now shows a parameter in place of a constant.

Table 1 shows only the first four columns of the output of my query in Listing 1. There is actually a fifth column that isn’t in the table because the column is too wide. This column is the XML query plan returned by the function sys.dm_exec_query_plan. If you’re displaying your results in “grid” mode in SQL Server Management Studio, the XML plan is shown as a link, and you can click the link to display a separate window containing the XML document. If you click the link for either ad hoc plan, you’ll get only a truncated query plan, something like that in Figure 1. The full XML plan, showing all the columns returned at each step and the constant value that the plan was compiled with, is about 276 lines long and is returned in the XML for the prepared plan.

In this case, the two ad hoc plans have a corresponding prepared plan containing the full XML plan. However, there are cases in which you’ll have an ad hoc plan for which the full XML isn’t available at all in cache. To see an example of such as case, run the code in Listing 3, which clears the plan cache again and runs a query that isn’t considered “safe.” There is a nonunique, nonclustered index on CustomerID, and some CustomerID values might occur much more often than others. For this reason, SQL Server’s optimizer cannot be sure that the plan it generates for one specific value of CustomerID will still be the best plan for another specific value of CustomerID. No autoparameterization is done, and if you run the code in Listing 1 again, the output will show only an ad hoc plan. Clicking the link of the XML plan shows only the truncated plan. The only way to see the full XML plan for this query is to turn on either SHOWPLAN_XML or STATISTICS XML prior to running the query, then look for the plan in the results window. The plan won’t be available in sys.dm_exec_cached_plans.

Truncated Plans
Not all non-parameterized queries provide only the truncated plan in cache. When I first started exploring the plan-cache metadata, I was very frustrated because sometimes I’d see the full XML for the ad hoc plan, and sometimes I wouldn’t. Just recently, I realized what the difference was. There are two main reasons that SQL Server won’t autoparameterize a query. As I mentioned earlier, queries won’t be autoparameterized if the optimizer doesn’t consider them safe. In addition, even if there’s only one possible plan for a query, if the query contains any of a list of particular query constructs, the query won’t be autoparameterized by default. “Controlling Parameterization” contains a list of these constructs; following is a partial list:

• IN clauses
• SELECT statement with INTO clause
• SELECT statement whose SELECT list contains a DISTINCT
• statement with the TOP clause
• when a SELECT query contains a subquery or JOIN
• when a SELECT statement has GROUP BY, HAVING, or COMPUTE BY
• expressions joined by OR in a WHERE clause

If your query contains any of these elements, it will not be autoparameterized unless you’ve set your database to FORCED parameterization or you have a plan guide that uses a PARAMETERIZATION FORCED hint, which I describe in “Controlling Parameterization.”

Listing 4 contains three very simple queries, but each one contains a construct in the previous list. The first query combines the two autoparameterized queries from Listing 2, but combines the two constants in an IN clause. The second query performs a straightforward JOIN that returns only one row. The third query aggregates the data in the Sales .SalesOrderDetail table using a GROUP BY. Running the code in Listing 1 after running the code in Listing 4 should show three ad hoc plans. However, unlike the query plan from the ad hoc query in Listing 3, clicking the XML plan links for the queries in Listing 4 should show the full XML query plans for each of the three queries.

Ad hoc plans in cache will result in a truncated XML plan if there’s a corresponding prepared plan, or if the plan is a simple one and considered unsafe for autoparameterization. Ad hoc plans that can’t be autoparameterized because they use one of the disallowed constructs will provide the full XML plan in cache.

Keeping an Eye on Query Plans
One of the benefits of using the plan cache metadata is to allow you to see plans for queries that have been run by any user, as long as the plan is still cached. You don’t need to run the query yourself and include the SET options to return the plan. In addition, having the plan cache metadata available allows you to compare many plans at the same time. In my next article, I’ll continue to examine plan cache and show you some of the available metadata to find queries that are run often and queries that take the greatest amount of resources during execution.

End of Article