This article is taken from the book SQL Server DMVs in Action. The author describes how to find the amount of space in tempdb in order to prepare for future space usage.
Get 40% off any version of SQL Server DMVs in Action with the checkout code dnspark40. Offer is only valid through www.manning.com.
When a SQL query (batch or stored procedure) is run, the optimizer determines how it will fulfill its needs. Typically, the optimizer looks at the number of rows in the tables, the indexes, and statistics and determines the best way to access the table's data.
Viewing the cached plan associated with a SQL query is a great way to discover why a query is behaving like it is. Why is the query taking too long? Is it using an index or a table scan? Is an index being used appropriately (an index seek rather than an index scan)?
The script we use to find a cached plan is shown in listing 1.
Listing 1 How to find a cached plan
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20
st.text AS [SQL]
, COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value AS INT))+'*',
'Resource') AS [DatabaseName]
, cp.usecounts AS [Plan usage]
FROM sys.dm_exec_cached_plans cp #A
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
WHERE pa.attribute = 'dbid'
AND st.text LIKE '%PartyType%' #B
#A Join cached plan and SQL text and query plan DMFs#B Text to search plan for
In the listing, you can see that there is one Dynamic Management View (DMV) and three Dynamic Management
Functions (DMFs) involved in finding a cached plan. A brief description of each is shown in table 1.
Table 1 DMVs/DMFs for finding a cached plan
DMV / table Description
sys.dm_exec_cached_plans Contains cached plans
sys.dm_exec_sql_text DMF that returns the sql text identified by a given plan handle sys.dm_exec_query_plan DMF that returns the cached plan in XML format, identified by a
given plan handle
sys.dm_exec_plan_attributes DMF used to obtain the underlying database for ad-hoc or prepared queries (as opposed to stored procedures)
The joining of the DMV and DMFs provides enough information to identify the cached plans for the specified text pattern given by the LIKE clause, across all databases on the server. The DMV sys.dm_exec_cached_plans provides the plan_handle used to CROSS APPLY to all three DMFs. The cached plan's plan_handle is passed to the DMF sys.dm_exec_sql_text to retrieve the text of the SQL query. Similarly, the cached plan's plan_handle is passed to the DMF sys.dm_exec_query_plan to retrieve the cached plan of the query. Finally, the cached plan's plan_handle is passed to the DMF sys.dm_exec_plan_attributes to decode the underlying database name for any ad-hoc or prepared queries.
We use a common pattern to extract the underlying database name for any ad-hoc or prepared queries.
The query we execute selects the first 20 queries that contain the text PartyCode in the SQL query across all databases on the server. This query can be very useful when searching the SQL queries for a given comment; for example, a given change number, change implementer, or date of change.
LIKE pattern matching
There is one thing you should be careful about when searching for names that are wrapped in square brackets or have an underscore, as is sometimes the case in SQL Server function names; for example, [dbo].[Some_Function_Name]. When a LIKE statement is used, it interprets the underscore to mean any single character, and the square brackets to mean any single character within the set.
An example of the type of output for this query is given in figure 1:
Figure 1 Output showing cached plans that contain the text PartyType
The presented script could be amended to filter for cached plans on a given database only. We can also filter on the objtype to look only at non-stored procedure SQL queries, if we are interested in the amount of ad-hoc queries running on the server.
Several SQL scripts can use be used to identify SQL queries that are problematic from different perspectives. Each perspective identifies queries that would benefit from further investigation, with the aim of improving their performance. This article provided a SQL script that can use be used to identify a cached plan.
SQL Server DMVs in Action
EARLY ACCESS EDITION
Ian W. Stirk
MEAP Release: February 2010
Softbound print: Winter 2010 | 375 pages