brandon_ledbetter (7/13/2010)
Thanks - good to have a name to identify the problem with. Suprising that the DBA's at the MS tech conferences (PDC, local TechFest, etc) I have been to weren't able to name the problem so fast...Also, I have wondered if there was a way that they could just delete the suspect plan from the cache, and leave the rest alone, but the users are just happy to have the timeouts stop, and recompiling the queries doesn't seem to be heavy enough to warrant the time for me to investigate if this can be done, and how.
Yep, you can identify the plan in cache and issue a drop for just that plan based on the sql_handle or the plan_handle. This is from BOL
DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ]
Since you are running into something that sounds like, might not be, but sounds like, parameter sniffing, you could try some of the more traditional parameter sniffing solutions, such as using a query hint OPTIMIZE FOR, etc. But, you may still wind up having to force the plan as you did.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning