Most of you guys know that you can clear all cached plans using DBCC FREEPROCCACHE.
In SQL Server 2008 you are able to clean a single pan from cache. The query below helps you to find the plan.
SELECT st. text, qs.query_hash,'DBCC FREEPROCCACHE(' ,plan_handle ,')' FROM sys .dm_exec_query_stats qs CROSS APPLY sys .dm_exec_sql_text( qs. sql_handle) st WHERE st. text LIKE 'SELECT * FROM Person.Address%' -- your query
There is full example in the Books Online, so you might want to read the following link:
Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log will contain the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to ‘DBCC FREEPROCCACHE’ or ‘DBCC FREESYSTEMCACHE’ operations." This message is logged every five minutes as long as the cache is flushed within that time interval.
You can also clear all the plans for one particular database from cache. You can use the command:
In the next post I will talk about how to force a specific execution plan.
I know that it’s a old subject, but I still see many people asking about it in the SQL Server community.