Ad hoc queries and Plan Cache Pollution

Just sharing a link on why we should not issue random ad hoc queries against Production - pollutes the plan cache...I have seen instances when a random ad hoc report query run by a developer brought down Production to its knees...


http://blog.sqlauthority.com/2010/08/28/sql-server-plan-cache-retrieve-and-remove-a-simple-script/

Excerpts:

Query that demonstrates cache plans which are ‘ad hoc’ or called only once in a life time.


SELECT 
    [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
   AND cp.objtype = N'Adhoc'
   AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;



You can see how much memory is already bloated by not-so-useful queries. If you want to remove any large plan cache which you do not think is useful to you, you can run the following command to remove it:

DBCC FREEPROCCACHE(plan_handle)

Note: Do not play with this settings on production server unless you know what you are doing.