Flushing only one query plan

Question

Post reply

Flushing only one query plan

monster

SSC Enthusiast

Points: 119
More actions
June 14, 2007 at 4:29 am

#117132

Is there a way to retire or flush a single cached query plan?
We had a problem this week, where a bad query plan had been stored for a frequently executed query. The first time it ran, it was for a yearly time sheet instead of a weekly time sheet. This made a bad plan query plan the rest of the queries for weekly time sheets.
We were able to find the query plan using sys.dm_exec_requests and sys.dm_exec_query_plan()
The only thing we could think of was to use dbcc freeproccache, but that isn't really a good option on a production database.
Can I find and remove a single cached plan?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I take it we're talking about a stored procedure here? If so, just use sp_recompile.

John

monster SSC Enthusiast Points: 119 More actions · Answer 2

Unfortunately it's not a stored procedure, but a parameterized query.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

Gotta love embedded SQL... and not trying to be a snit about this... check out the discussing beginning to unfold at the following URL... it's similar to many other discussions I've seen on the web...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=373182&post=true

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)