Max Server memory - which buffers and caches is covered

Question

Hi,

I found that my knowledge of what is controlled by the max server memory is rather dated. I was under the impression that it was only the buffer pool that the max server memory defined the size of.

But I learn now that, that is not the case.

I have unsuccessfully tried of find a complete overview of what is actually controlled by the max server memory setting. The buffer pool , the plan cache, query work buffer etc. ?

Do we have other memory areas that is not under the control of the max server memory setting ?

Which means do we have to manipulate the default memory allocations of the SQL server besides the max server memory setting ?

sp_configure 'min memory per query (KB)', 1

various trace flags

Other ?

Thank you for your assistance :)

Answer 1

Hi @agepeder,

Have you read this Server memory configuration options which contains several articles about the memory for SQL Server?

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

It depend on SQL Server version, what all is controlled by max memory setting, see e.g.

https://learn.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide?view=sql-server-ver16

=> Changes to memory management starting with SQL Server 2012 (11.x)