SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script

I had a very interesting situation at my recent performance tuning project. I realize that the developers there were running very large dataset queries on their production server randomly. I got alarmed so I suggested their developer not to do that on the production server; instead, they could create some alternate scenarios where they could synchronize database and query on the same server. The production server should not be used for development work. It should be queried with proper methods (queries, Stored Procedures, etc.), supporting production application.

The lead DBA, who claimed he has 19 years of experience, pointed out, “What is wrong in running ad-hoc queries if their server is not having any pressure at the moment?” When someone starts talking about his “experience” to me, I always feel confused. How can you compare experience over expertise? You might have learned swimming when you were 3 years old and might have 27 years of experience of it, considering you are already 30 years old now. However, this is apparently mediocre when compared to someone who might have learned swimming just 5 years ago, yet participates in state competitions today. After the long conversation, I was able to convince him that running queries not related to the production would just pollute his cache with unnecessary cache plans.

Here is the 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)

You can find the plan_handle from the first query where the third column is indicating the plan handle.

Here is a quick display of my machine’s plan handle and how I can remove any plan from the cache:

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

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)