Can we remove a single query plan

Can we remove a single query plan?

You can use sp_recompile system stored procedure to clear it if the type of query plan is proc,trigger or function

Else you can't. If the received parameter of sp_recompile is a object(such as table), sql server will regenerate a new query plan when the table was refer next time, but the current plan will not be removed.

You can use DBCC FREEPROCCACHE the remove all the query plan cached in memory

or

use DBCC FLUSHPROCINDB to remove cached plan of specific database

 

How to get the size of cached plan?

For the single cached plan, size_in_bytes from sys.dm_exec_cached_plans dmv can help you.

For the whole procedure cache, run the DBCC MEMORYSTATUS command

In The Procedure Cache section

 

How to get the number of cached plans

use the DBCC MEMORYSTATUS command

In The Procedure Cache section

Or run following script

select *from sys.dm_os_memory_cache_entries where type in

('CACHESTORE_OBJCP','CACHESTORE_SQLCP',

'CACHESTORE_PHDR','CACHESTORE_XPROC')