Posted By

tvanzele on 04/16/10


Tagged

sql DMV


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Tyster


List of single-use, ad-hoc queries that are bloating the plan cache


 / Published in: SQL
 

  1. SELECT TOP(100) [text], cp.size_in_bytes
  2. FROM sys.dm_exec_cached_plans AS cp
  3. CROSS APPLY sys.dm_exec_sql_text(plan_handle)
  4. WHERE cp.cacheobjtype = 'Compiled Plan'
  5. AND cp.objtype = 'Adhoc'
  6. AND cp.usecounts = 1
  7. ORDER BY cp.size_in_bytes DESC;

Report this snippet  

You need to login to post a comment.