Evict Query Store Plan from Plan Cache

Once in a while you get a bad plan in your plan cache.

This time you caught it in time in Query Store under Regressed Queries, and it seems to be a parameter sniffing issue. The nightly batch job wrecked your plan cache, and the first query this morning just had to be the one forcing a hash-join over the Sales table... ugh.

What to do? Well, you could force the better plan (in the query store) - and in fact maybe you should. Why risk getting stuck with a bad plan, if you can force a better one? I dont' know - but maybe you would like for the plan to evolve along with your data?

Another - and less permanent solution is to just evict the bad hash-joining-performance-wrecking plan from the plan cache. But how?

1. find the Query-store Query ID 
2. SELECT query_plan_hash FROM sys.query_store_plan WHERE query_id=@queryId (from 1.)
3. SELECT plan_handle FROM sys.dm_exec_query_stats WHERE query_plan_hash = @planHash (from 2.)
4. DBCC FREEPROCCACHE(@planHandle)  (from 3.)

select plan_id, concat('DBCC FREEPROCCACHE (', Convert(VARCHAR(1000), deqs.plan_handle,1),')')
from sys.query_store_plan qsp 
	join sys.dm_exec_query_stats deqs ON (qsp.query_plan_hash = deqs.query_plan_hash)
where query_id=@queryId

This would produce 0 to N possible eviction commands. Use the planIds in the Query panel legend to figure out which one(s) to remove.

 

Comments are closed