1

So memory use is hitting limits. Would clearing the database memory cache on a daily/weekly schedule be of use?

(Ignoring the fact I probably should be looking at reducing size of database/indexes)

Background:

Bunch of SQL Server database (all on one SQL Server instance), one server, and some memory. Tons of data is written to database, but most of this is just relevant to the current day, its a monitoring system thing. So over a couple of days, the cache will be eaten up by info of the previous day, which may or may not be needed/viewed by anyone for some time, or ever.

So as it writes data, and checks what's in database, it compares against data of the current day. Once the day rolls over, it not comparing against that data any more.

So my thinking is that by clearing the memory at the end of the day, the memory use in the next day will only be relevant of what needed? Would this be suitable case of FreeSystemCache/DropCleanBuffers

And yes - long term would be better to look a reducing size of database/indexes, however that also falls into what the data being stored is for, which actually makes archiving of data somewhat tricky without some interlinked program logic.

4

Would clearing the database memory cache on a daily/weekly schedule be of use?

The straight answer is No. This would be a very bad idea, I suggest you don't do this.

So my thinking is that by clearing the memory at the end of the day, the memory use in the next day will only be relevant of what needed? Would this be suitable case of FreeSystemCache/DropCleanBuffers

You probably need to understand how SQL Server memory works. SQL Server caches as much data pages as possible to avoid physical I/O, which of course is costly. If you clear memory used by database somehow, next day when it has to read pages it would have to bring it back into memory forcing I/O, perhaps lots of I/O on a busy system. Please note reading of database pages is always done in memory. I would also question your assumption about being sure that pages which are read today won't be, in any case, required tomorrow. This is not a correct idea.

It does seems like SQL Server is holding memory so how would it cater to new requests? This is simple, if your request needs pages which are already in memory, read would be done in memory. If the page is not there in memory I/O would be issued to bring it in memory and if space is not there in memory, least used pages would be flushed out to the disk to create space. This happens many a times on busy system without affecting performance.

It's costly for SQL Server to keep on taking and releasing memory because this process involves CPU and other resources; so SQL Server limits these by grabbing as much as it can in cache.

long term would be better to look a reducing size of database/indexes

If you take my advice and considering you don't have unused indexes, it would be better to increase RAM on system and provide more to SQL Server. Yes, of course your database design must be of a standard which does not have redundant indexes, and properly sized data and log files with an appropriate autogrowth value.

  • okay thanks for good info. now here is a very simplified example that I was trying to tackle: given 2 databases: DB1, DB2 DB1 is used every day, so over 1 week - DB1 MIGHT cache info over 7 days. DB2 only has info written/accessed on Tuesday. So come Wednesday - the memory that DB2 takes up is not actively used. However DB1 now requires memory. By using least used pages - memory would be released related to DB1 from Monday. However what I would prefer is all of memory used by DB2 to be released. – Maze Sep 28 '15 at 12:56
  • Also: yes get that SQL server is trying to be decent with what it actually releases. RAM boost only so good when on a "high spec machine" (5 years ago). Also I already know how to reduce database size - which requires business to actually budge and allow the changes to be implemented. – Maze Sep 28 '15 at 12:57
  • Ok if you want memory by DB2 released run dbcc dropcleanbuffers this does not requires instance restart. This would not remove committed memory but you would see most of it which sql server was holding released. You must also set max server memory to lower value on DB2 instance so that you can upfront cutdown its consumption from beginning. – Shanky Sep 28 '15 at 13:10
  • hopes then dashed. By DB1 and DB2 i was meaning in ONE sql server instance is two databases DB1 and DB2. thanks for the help anyway. – Maze Sep 28 '15 at 16:36
  • In that case you don't have much option – Shanky Sep 29 '15 at 5:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.