Would daily/weekly db memory cache clearing be of use?

Question

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.

Answer 1

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.