Sql Server's Buffer Pool is a wonderful thing. It's smart enough to handle all sorts of situations in a fairly intelligent way. Here are a couple examples showing how at first glance the buffer pool behavior seems strange, but is actually fairly clever.
Consider a 400 GB clustered index on a server with 64 GB of Memory available for the buffer pool. If a query scans the entire 400 GB index, how much buffer pool should the index use after the query is complete?
Probably not very much. If Sql Server aggressively cached all the data it accessed maybe the last 50GB that it read during the scan is in memory. However, the next time you run the same query, that 50GB is useless because Sql will have to read the first 350GB from disk before it gets to the last 50GB. This is a common enough scenario that Sql handles it intelligently. Instead it is more likely that all of the intermediate level index pages will be cached along with a few of the leaf pages.
I've got a server with 512GB of memory, but it never seems to use all of it, or it uses it..strangely?
Sql Server is NUMA aware. On NUMA (Non-Uniform Memory Access) systems, some memory is closer/faster to a CPU than other memory. For example, a 4 socket system might have 4 memory controllers (1 per socket). It is faster for a CPU to access memory on the controller attached to it vs accessing memory through some other cpu's controller. (I've seen 1.4-1.7 times slower access).
Sql Server knows this, and will prefer to allocate local node memory before remote node memory. Since requests can run on any cpu (they don't typically jump between them) and hence allocate memory on any node, the first request to access an index will cache it's pages in local memory. In most cases, you'll end up with a fairly equal distribution of cached pages, however, each node is managed individually by sql server, so effectively, your 512 GB is really four 128GB buffer pools. You could have the situation where node 1 is using all 128GB, node 2 is using 80GB, and pages are getting discarded from node 1 to make room while node 2 has lots of free space.
Why would my heavily read from indexes be cached less than indexes only receiving writes?
One possible reason is checkpoints. Depending on your recovery interval, it may be several minutes after a data change occurs before a checkpoint writes the page to the data file (it's already in the log file on disk). During this time, the page can't be discarded to make room in the buffer pool.
Besides setting the size, are there other ways to change how the buffer pool behaves?
There isn't a whole lot of control over the buffer pool and the decisions it makes. That's fine because you typically don't need to tweak it. Watching perfmon counters for "SQLServer:Buffer Manager" and "SQLServer:Buffer Node" can provide insight into what the buffer pool is doing, whether it is sufficient in size, whether your IO subsystem can keep up with the checkpoints.
Regarding a couple of items you mentioned:
- Fragmentation on disk doesn't affect memory usage directly. However, if an index has 50% free space on the pages due to deletes, it will take up twice a much room on disk and in memory, but that's related to free space, not fragmentation.
- PAGE_IO_LATCH Waits measure the time it takes for the IO subsystem to load pages into memory. They are an indicator of disk performance and not memory.
- You are correct, high page lock waits are due to the writes. The writes make pages dirty, and dirty pages have to stick around in the buffer pool until they get checkpointed to disk.
The problem is that you make several wrong assumptions. Below you will find some corrections
MongoDB is not optimized for small resources
MongoDB was specifically designed to take a lot of data (recording clickstreams were the first application, iirc). As far as I can read, you have your django app on the same server as MongoDB. The problem here is that a lot of users for your django app would translate in a lot of queries/aggregations/write operations done on the MongoDB side. So django and MongoDB would have a race for resources especially during high load times. Since django is the first in the stack, it will almost always "win", for example requesting RAM which MongoDB now can not request. So it might well happen that MongoDB refuses something because of the lack of resources, the request is cancelled and your system appears to do nothing while really the two parts of your application did their best to answer the request, but failed to do so for the lack of resources.
To be honest: Running MongoDB on an 1GB instance alone would imho be not reasonable. Let alone with a django application. Imho, with this setup, you should at the very least have 4GB of RAM, and it might work until you put real load on it. For comparison: I usually suggest between 32 and 128GB of RAM per node (depending on the data, indices and a few other factors) for machines using SSDs as storage technology. Mind you, that is for MongoDB only – at an according scale of data, of course.
"Working set" does not (only) mean cache
Disclaimer: brutally simplified and terminology might be off
MMAPv1 uses memory mapped files. All the details put aside, this means that a file is treated as an addressable range of memory. So if MongoDB wants to read a certain doc, it uses a memory address and a range it wants to read. That memory address might either be already in RAM or it has to be read from disk. Or, and here is the misconception, from the OSes filesystem cache, which – you guessed it – resides in RAM, though just a different part. (Iirc, what happens in this situation is that the address a pointer refers to is changed). So, not only would MongoDB have the working set in RAM, but it would be the direct cause of quite some part of the filesystem cache. So, we have another part of MongoDB requiring even more RAM than the working set only.
The working set is not the only thing consuming memory
- Actually, the way journaling works, it doubles the RAM required by MongoDB.
- Each connection (and remember each driver basically opens a connection pool) gets 1MB of RAM allocated.
- Operations need some memory. Lets take aggregations as an example. They are capped to 100MB memory consumption – that alone would be 10% of your RAM, 5% of your allocatable memory.
However since you use MMAPv1: Do NOT turn off journaling! It is vital for crash recovery in MMAPv1.
Conclusion
Your machine is vastly underprovisioned in terms of RAM. Even if you have a tight budget, I can not stress the need of putting more RAM into that machine enough. I'd at least put 4GB into that machine (physical, that is, not swap) and see how it goes.
Be aware though, that with this setup, you'll always have django and MongoDB compete for resources the most when you need it the least: when your application has comparatively many concurrent users.
Best Answer
The straight answer is No. This would be a very bad idea, I suggest you don't do this.
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
alwaysdone 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 pageswould 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.
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.