DBCC FreeProcCache

Question

DBCC FreeProcCache

sumeetahluwalia

SSC Enthusiast

Points: 139
More actions
June 23, 2014 at 11:07 pm

#396953

Hello,
My knowledge of SQL is very limited. One of our old co-workers ran DBCC FreeProcCache on our database in a test environment. I am not sure what impact it had but every time we login to our application it just hangs there, what I am looking for is an explanation as to what this command does? I have googled this topic, it seems to delete everything in SQL Cache and mentions recompilation of stored procedures. Do I have to recompile all SPs and if so, what is the commandline or query to do this, any help would be appreciated.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It removes every single cached plan, So next time the queries run, they have to go through the expensive compilation process again.

It is NOT something that should be run on production.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 2

There's nothing you can run specifically to reload queries into cache. You running the app is doing that work for you. In short, this command is not something that should be run lightly. If you are hitting a situation where you want to force an individual query to recompile, you can query the cache for the plan handle (using sys.dm_exec_query_stats, sys.dm_exec_procedure_stats or sys.dm_exec_requests) and then pass that plan handle to freeproccache to remove that one plan. That's the place where I would recommend using it in production, targeted and precise. Other than that, I'm with Gail, never run it in production.

"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

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

Have you checked the application side? I suspect it's coincidence. As soon as you execute the SP first time it will take little longer due to compilation but next execution it will be into CACHE and should not have any issue.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

sumeetahluwalia SSC Enthusiast Points: 139 More actions · Answer 4

Thank you all. So I understand this should not be taken lightly, I actually did it in our test environment. But we use this test environment to deploy new patches/releases. Upon logging in, I am not getting a Fast CGI error. I will look through this, maybe its not a SQL issue. Thank you all.