DBCC FREEPROCCACHE

Question

DBCC FREEPROCCACHE

Steve Jones - SSC Editor

SSC Guru

Points: 736478
More actions
July 13, 2018 at 3:24 pm

#366132

Looking for a basic article on how to use this, especially why not to do this on prod, or any server that needs plans in cache, as well as how to find and remove one plan. Perhaps show some param sniffing, decide to remove one plan and then re-run the proc with a parameter to get a "good enough" plan.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Steve Jones - SSC Editor - Friday, July 13, 2018 3:24 PM
Looking for a basic article on how to use this, especially why not to do this on prod, or any server that needs plans in cache, as well as how to find and remove one plan. Perhaps show some param sniffing, decide to remove one plan and then re-run the proc with a parameter to get a "good enough" plan.

Heh... I'm not sure what the paranoia is about running it in prod.

--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)

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

Often you wouldn't want to free all the plans in your cache in a production server.

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

Steve Jones - SSC Editor - Monday, July 16, 2018 7:36 AM
Often you wouldn't want to free all the plans in your cache in a production server.

Yep... I get that's what people say. I'm just not sure why they think that way. It's been more of a cure than a cause of problems for me especially since my main system is a combination of some decent hit rate OLTP and some fairly large and complicated batch runs that run all day every day.

And, no... I'm NOT recommending that it be used on a regular basis. But, the "Perfect Storm" does occasionally occur and the code affected varies greatly. In those cases, "Launching the Nuke" has worked small miracles and other than making things a whole lot better, has had no discernible negative impact on my systems.

My disclaimer is that I may not be taking the extremely heavy hits that some folks systems may be taking and so I don't have the experience there to say that you should never do such a thing on such systems. That's also my excuse for saying that someone that does have such experience should write the article and not me. 😉

--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)