DBCC FREE**** - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

DBCC FREE****

Author

Topic

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2011-04-28 : 22:20:24

What is the difference while executing especially with reference to releasing of the memory with

DBCC FREEPROCCACHE and DBCC SYSTEMFREECACHE

Any precendence over other, so what?

Thanks

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-29 : 13:59:31

DBCC FREECACHE

Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool

DBCC FREESYSTEMCACHE

Releases all unused cache entries from all caches. The SQL Server Database Engine proactively cleans up unused cache entries in the background to make memory available for current entries. However, you can use this command to manually remove unused entries from all caches or from a specified Resource Governor pool cache.

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-02 : 04:19:39

You should never practice this on your Production Servers. As creating a plan is more expensive function of Query Optimizer and any of above command will remove all cached plan from memory and resultantly all of your queries will become slow because each query have to regenerate a plan from scratch.

--------------------------
http://connectsql.blogspot.com/

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2011-05-03 : 00:34:16

The server became slow then to release the memory related for the SP I have used the FreeProcCache and for System used with the parameter 'TokenAndPermUserStore'.

But down the line the user was happy thereafter.

I just concluded with what difference either of the these commands would be perfect.

I have executed on the production server and we dont have any cached plans so we are not bothered. however, the purpose served.

But want to know what kind of precedence will occur.

Thanks All.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-03 : 01:52:17

You don't have any cached plans? Sure about that?
SQL always caches plans so that it doesn't have to spend the time and CPU creating plans every time. Don't run that on production without a really good reason. It'll slow things down while SQL recompiles and re-optimises all queries

The difference is just down to what cache they clear. FREEPROCCACHE frees the plan cache (all or part), the cache of all optimised execution plans. FREESYSTEMCACHE takes a parameter that lets you specify which cache to clear.

As for TokenStore: http://sqlinthewild.co.za/index.php/2007/10/21/memory-and-sql-2005-sp2/ and http://sqlinthewild.co.za/index.php/2008/05/02/token-and-perm-user-store-growth/

--
Gail Shaw
SQL Server MVP

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2011-05-03 : 18:52:57

Cache Plans were unwanted then only I have issued this FreeSystem Cache, however, How to spare though issuing the command.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-03 : 18:59:24

How do you determine that every single plan in cache is unwanted?

As for how to avoid it, well that depends on why you decided to run it in the first place.

--
Gail Shaw
SQL Server MVP

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2011-05-04 : 00:25:37

Thanks Gail.

Yep. Once we decided to throw them we can use them. Got it.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-04 : 01:35:08

What? I wasn't telling you when to run it, I'm trying to figure out why you're running it in the first place when it is going to degrade performance until SQL recompiles all its queries.

I really don't think you understand SQL's caching, why it caches data and plans.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources