3

Does max server memory for SQL Server 2016 contain buffer pool plus in-memory columnstore plus in-memory OLTP, etc. cache?

4

Please visit this link. There are exact explanations about the memory limits especially in confunction with SQL Server 2016 SP1.

In simpler terms, buffer pool memory in SQL Server is a memory cache responsible for caching data and index pages for the entire instance. The rest of the caches in the SQL Server memory (procedure cache, thread stack, backup buffers etc) consumes memory outside buffer pool memory. The memory consumed by caches outside buffer pool is not restricted by above memory limits and can grow up to limits defined by "max server memory". This is not specific to SQL Server 2016 SP1 and is also applicable to earlier releases of SQL Server as well.

And

For example: A Standard Edition of SQL Server has buffer pool memory limited to 128GB, so the data and index pages cached in buffer pool is limited by 128GB. Starting with SQL Server 2016 SP1, you can have an additional 32GB of memory for Columnstore segment cache per instance and an additional 32GB of memory quota for In-Memory OLTP per database. In addition, there can be memory consumed by other memory consumers in SQL Server which will be limited by "max server memory" or total memory on the server if max server memory is uncapped.

1
  • Thanks sp_BlitzErik. Is max server memory upper limit for all cache or in-memory oltp can consume available memory on os? Jan 12 '18 at 12:37
1

I believe it depends on what you set max memory to, what is available on the host, and what edition you have. Aaron Bertrand wrote a good blog post on exactly this:

https://sqlperformance.com/2016/12/sql-server-2016/memory-limits-sp1

To try and answer your question, "If the server has less than 128GB, you will see these technologies compete with buffer pool memory, and in fact be limited to a % of max server memory."

1
  • Thank a lot DimPerson.In this case, server has 64 gb mem with sql 2016 standard and I set max server memory to 58 gb this setting will include both in-memory oltp and columnstore right? Jan 12 '18 at 12:10
0

For SQL 2012+, all memory for SQL Server comes from the sp_configure max memory setting.

For In-Memory OLTP, the SQL 2016/SP1 limits for non-enterprise editions can be confusing. Some of the limits are per-instance, while others are per-database. Columnstore for on-disk tables counts towards the per-instance cap, but columnstore for memory-optimized tables counts towards the per-database cap.

In Standard Edition, you cannot have 32GB of memory-optimized data plus 32GB of columnstore indexes within a single memory-optimized database.

Extremely important to note is that if your memory-optimized database also has an on-disk workload, then the memory-optimized footprint can grow to a point where it puts pressure on the buffer pool, and this can affect on-disk workloads.

The amount of memory available to all memory-optimized databases on a SQL Server instance is listed here: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/bind-a-database-with-memory-optimized-tables-to-a-resource-pool

If you run Standard Edition and configure max memory for SQL Server to be 58GB, then due to the Edition-level caps, a single database won't be able to use all that memory for In-Memory OLTP. If you had an on-disk workload, then some of the memory > 32GB would be allocated to the buffer pool. But if you don't have an on-disk workload, you might not ever make use of the additional memory.

I have a blog post here that details some of this: http://nedotter.com/archive/2018/01/in-memory-oltp-resources-part-4-oom-the-most-feared-acronym-in-all-of-in-memory-oltp/

Please let me know if you have further questions.

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.