Microsoft SQL Server 2008 Internals in VB.NET

Creating GS1 - 12 in VB.NET Microsoft SQL Server 2008 Internals

You should see two rows with the same text string and sql_handle, but with different plan_handle values, as shown here. (In our output, the difference between the two plan_handle values is only a single digit so it may be hard to see, but in other cases, the difference may be more obvious.)

-- this is an example of the -- relationship between -- sql_handle and plan_handle SELECT LastName, FirstName, Country FROM Employees WHERE Country <> USA -- this is an example of the -- relationship between -- sql_handle and plan_handle SELECT LastName, FirstName, Country FROM Employees WHERE Country <> USA

We can see that we have two plans corresponding to the same batch text, and this example should make clear the importance of making sure that all the SET options that affect plan caching should be the same when the same queries are executed repeatedly. You should verify whatever changes your programming interface makes to your SET options to make sure you don t end up with different plans unintentionally. Not all interfaces use the same defaults for the SET option values. For example, the OSQL interface uses the ODBC driver, which sets QUOTED_IDENTIFIER to OFF for every connection, whereas Management Studio uses ADO.NET, which sets QUOTED_IDENTIFIER to ON. Executing the same batches from these two different clients results in multiple plans in cache.

The function sys.dm_exec_query_plan is a table-valued function that takes a plan_handle as a parameter and returns the associated query plan in XML format. If the plan is for an object, the TVF includes the database ID, object ID, procedure number, and encryption state of the object. If the plan is for an adhoc or prepared query, these additional values are NULL. If the plan_handle corresponds to a Compiled Plan Stub, the query plan will also be NULL. I have used this function in some of the preceding examples.

The function sys.dm_exec_text_query_plan is a table-valued function that takes a plan_handle as a parameter and returns the same basic information as sys.dm_exec_query_plan. The differences between the two functions are as follows:

sys.dm_exec_text_query_plan can take optional input parameters to specify the start and end offset of statements with a batch. The output of sys.dm_exec_text_query_plan returns the plan as text data, instead of XML data.

9

The XML output for the query plan returned by sys.dm_exec_query_plan is limited to 128 levels of nested elements. If the plan exceeds that, a NULL is returned. The text output for the query plan returned by sys.dm_exec_text_query_plan is not limited in size.

The sys.dm_exec_cached_plans view is the one we use most often for troubleshooting query plan recompilation issues. It s the one I used in the rst section to illustrate the plan reuse behavior of adhoc plans compared to autoparameterized and prepared plans. This view has one row per cached plan, and in addition to the plan_handle and usecounts, which we ve looked at already, this DMV has other useful information about the cached plans, including the following:

cacheobjtype The type of the cache object; that is, if it s a Compiled Plan, or a Parse Tree or an Extended Proc memory_object_address The memory address of the cache object, which can be used to get the memory breakdown of the cache object

Although this DMV does not have the SQL Text associated with each compiled plan, we ve seen that we can nd it by passing the plan_handle to the sys.dm_exec_sql_text function. We can use the query below to retrieve the text, usecounts, and size_in_bytes of the compiled plan and cacheobjtype for all the plans in cache. The results are returned in order of frequency, with the batch having the most use showing up rst:

SELECT st.text, cp.plan_handle, cp.usecounts, cp.size_in_bytes, cp.cacheobjtype, cp.objtype FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st ORDER BY cp.usecounts DESC;