We are observing something strange with our leaf node memory utilization.
We have a database with a couple schemas on it, each containing some data. We see “TOTAL MEMORY USAGE” of around 15GB of memory in Studio.
We then create a new schema. We do not populate the new schema with any data at all. It has a fair number of tables (about 1,000) but Studio shows only 9MB for memory usage in the Databases view.
However, we see the “TOTAL MEMORY USAGE” goes up to around 19GB of memory in Studio when we add this schema. Dropping the schema reclaims the memory.
What are we doing wrong that causes so much memory to be consumed by a database with a lot of tables, but where all tables are empty?
Table metadata, initial rowstore structures (empty ones) and table code objects for the tables can take memory even for empty tables.
In 8.5, we made the table code objects evictable so if you have tables that are not used for the plan cache expiration interval, they can be evicted from memory. We’re continuing to improve this; recently we made it work for tables with unique keys. Will make it work for ALTERED tables soon.
It makes sense to me that table metadata would occupy some space in memory. But does the order of magnitude sound correct to you? Again, it is about 1,000 tables - would you expect the metadata alone for those to take 4GB of space, i.e. 4MB of metadata/initial rowstores/table code objects per table?
If that is in line with what you might expect, then my question is answered. If that number seems high to you then perhaps I need to look deeper.
I will also drop some of our current tables to see if there are certain outlier tables who take more space proportionally than others.
Yes, I would expect that. The code modules (generated code for insert/update/fetch) can take a couple of MB per node, in typical cases. But we have improved that a lot in 8.5, making those modules evictable from RAM if they are not used for a while. Please see docs on variables enable_idle_table_optimizations and enable_idle_table_eviction as well as plan_expiration_minutes. Variables controlling the size of the in-memory rowstore segment of columnstore tables may also have an impact.