A DMV a Day – Day 16

We have two DMVs for Day 16. The first one is sys.dm_exec_cached_plans, which is described by BOL as:

Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

The second one is sys.dm_exec_sql_text, which is actually a dynamic management function (DMF), that is described by BOL as:

Returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql.

Using these two together allows you to get a lot of insight into what is happening with your plan cache. Both of these work with SQL Server 2005, 2008, and 2008 R2. They both require VIEW SERVER STATE permission.

-- Find single-use, ad-hoc queries that are bloating the plan cache
SELECT TOP(100) [text], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE cp.cacheobjtype = 'Compiled Plan' 
AND cp.objtype = 'Adhoc' 
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

This query will identify ad-hoc queries that have a use count of 1, ordered by the size of the plan. It gives you the text and size of single-use ad-hoc queries that waste space in plan cache. This usually happens when your developers build T-SQL commands by concatenating a variable at the end of a “boilerplate” T-SQL statement.  A very simplified example is shown below:

-- Query 1
SELECT FirstName, LastName FROM dbo.Employee WHERE EmpID = 5

-- Query 2
SELECT FirstName, LastName FROM dbo.Employee WHERE EmpID = 187

Even though these two queries are essentially identical, you could end up with each of them having a separate plan in the cache, because the literal at the end makes it look different. Actually these two queries are so simple (with no joins) that SQL Server would probably parameterize them even with the default Simple parameterization enabled. Below is a more realistic, bad example:

  DECLARE @SortColumn datetime  
  DECLARE @LastValue int  
  SET ROWCOUNT 1  
  SELECT @SortColumn = isnull(InstantForum_Topics.DateStamp,''), 
  @LastValue = InstantForum_Topics.PostID 
  FROM InstantASP_Users (NOLOCK) 
  INNER JOIN InstantForum_Users 
  ON InstantASP_Users.UserID = InstantForum_Users.UserID 
  RIGHT OUTER JOIN InstantForum_Messages 
  INNER JOIN InstantForum_Topics 
  ON InstantForum_Messages.PostID = InstantForum_Topics.PostID 
  ON InstantForum_Users.UserID = InstantForum_Topics.UserID 
  WHERE InstantForum_Topics.TopicID = 34568 
  AND InstantForum_Topics.Queued <> 1   
  ORDER BY InstantForum_Topics.DateStamp, InstantForum_Topics.PostID  
  SET ROWCOUNT 21  

This query is doing several bad things, but it ends up being in the plan cache with a use count of 1, wasting space. Enabling ‘optimize for ad hoc workloads‘ for the instance can help (SQL Server 2008 only). Enabling forced parameterization for the database can also help, but you should test that in your environment.