Today I got to play with some really bad queries. But the queries weren’t necessarily bad, it was more they had bad plans. I thought I had already blogged about it and tried to find my code. Alas, it wasn’t there so let’s strip it down on how you would remove a bad query plan. I am leaving out the trouble shooting part of how to determine if it is a bad plan because so much of it “depends”.
First you have to find the bad query plan. Get a unique line from your query and paste it in the query below.
USE master; GO SELECT usecounts, cacheobjtype, objtype, text, plan_handle FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE usecounts > 1 AND TEXT LIKE '%Unique part of query%' --put the unique part of the query here. ORDER BY usecounts DESC;
Now, copy the plan handle and paste it over the plan handle that I have listed here:
SELECT * FROM sys.dm_exec_query_plan (0x060001004DE4D526F0BEA28F05000);
If you click on the query_plan link, you can see what the plan looks like. After you have reviewed it and determined the plan is bad then you can paste your plan handle over the one below to remove it from the proc cache.
DBCC FREEPROCCACHE (0x060001004DE4D526F0BEA28F05000)
There you have it. Best of luck with your bad plans.
[…] Andrea Allred shows one way of removing a bad query plan: […]
You should still look into why the plan is “bad”. Outdated statistics, skewed data distributions and parameter sniffing springs to mind.
You are absolutely correct. I wanted to show what to do if you needed to remove one, but understanding the reasons why is just as important. Thank you!