pros and cons of flushing the cache regularly | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

pros and cons of flushing the cache regularly

Hi!
I need to write a script which will flush the the cache on a scheduled basis.
Is there is any system stored procedure by which I can accomplish this
I am wondering what are the pros and cons of flushing the cache.More over in our enviorment new databases,their resp logins,user,db roles are created daily.So is it good to do the flushing after these operations.
Have a look at DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS.
I wouldn’t do this is a regular course of action on a production server. I think usually you do this on a test server to measure some duration or do other kind of development stuff. Why do you think you need to do this? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
Some of my senior thinks that flushing of cache can help in performance.Though I have a maint plan which do the checkpointing task.I am wondering what will be cons of doing these activities on production servers.
Flushing the cache removes *everything*. All data pages, execution plans… After such action it has to be reloaded into cache. I would be curious, why your Seniors think, this action can improve performance. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
It is nothing but you’re asking SQL server to start from scratch even for few good plans and it takes time to recompile the plans again and stress is on when the server is running resource intensive queries. If you keep on continuing the scheduled optimization jobs I don’t see any need to clear the cache until unless it is necessary and recommended. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Clearing sp cache is bad enough, I hope they didn’t recommend clearing data cache too.
The logic I got from them for flushing the cache is ..cache get filled very quickly and no more space left for the new database ,their sp,execution plans etc. But as per my knowledge sql server automatically handle this kind of issue.Point me if I am going wrong.
I already have a maint plan job to force the dirty buffers to write to disk for an instance of sql sevr.This script simply use checkpoint to achive this for all databases on server.Our server is restarted very rarely
Now is this kinda of buffer clearance is a bad idea?.Please suggest so that I can remove this job also.
Tell them to google for ‘lazywriter’. To be clear: Clearing the cache *is* generally a bad idea. SQL Server handles these things automatically. You use CHECKPOINT for something different. A CHECKPOINT flushes dirty pages to stable media, thus reduces the amount of work SQL Server would need to do in case of recovery. Unless you have a very busy OLTP environment, you hardly notice a CHECKPOINT in performance. So, I would say, it’s okay. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
Checkhttp://www.sql-server-performance.com/tp_analyzing_sql_server_2000_data_caching.asp article for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>