6

Documentation says:

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.

We are using columnstore so I expect that SQL Server will be limited by using 128+32 Gb of memory. Memory optimized tables are not used.

But in reality more than 215 Gb is being used for buffer pool: enter image description here

Memory Clerks enter image description here

Overall sql process takes ~300Gb. enter image description here

select * from sys.dm_os_process_memory

enter image description here

Max memory set to 360Gb. Server has 380Gb.

What am I missing?

Version:

Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64)
Mar 12 2019 19:29:19 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

UPDATE (2019-04-26) Here is interesting thing - MEMORYCLERK_SQLBUFFERPOOL is presented by two records. Row #3 is exactly what I'd expect to see for Standard Edition. But what is the first row? Server has 2 processors with 48 cores total. buffer pool

Improve this question
5
  • This may no longer apply but in this ms doc they say other internal and external components can show the sql server process to consume more memory than the max value. See the note halfwaydown MS Doc Again it may not longer be valid as this was posted in 2012.
    – Aaron
    Apr 24, 2019 at 22:41
  • It's a guess, but can you run this query and see if it returns anything? SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count], CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)] FROM sys.dm_os_buffer_descriptors WHERE database_id <> 32767 AND is_in_bpool_extension = 1 GROUP BY DB_NAME(database_id) ORDER BY [Buffer size(MB)] DESC;
    – Randi Vertongen
    Apr 25, 2019 at 8:08
  • Can you please add output of select * from sys.dm_os_process_memory in the question
    – Shanky
    Apr 25, 2019 at 16:13
  • @RandiVertongen - result set is empty.
    – Alex Bochkov
    Apr 25, 2019 at 20:16
  • @Shanky attached.
    – Alex Bochkov
    Apr 25, 2019 at 20:21

1 Answer 1

Reset to default
2

I believe you missed the documentation. Please see the highlighted points in image below

enter image description here

The memory used by memory optimized tables is 32 GB PER DATABASE

So let us say you are having SQL Server standard edition having columnstore indexes and having memory optimized tables in 3 databases.

The maximum memory utilized can be

128+32+(3*32)= 256 GB.

Let me know for how many databases you are using memory optimized tables ?. The 128 GB limit is only for buffer pool

Improve this answer
3
  • 3
    They only mentioned using columnstore. And if they had a large amount of in-memory OLTP data, it would show up in the memory clerks as MEMORYCLERK_XTP (which is not in their top 10 clerks output).
    – Josh Darnell
    Apr 25, 2019 at 17:34
  • Interesting! Do you have any idea why they put it at a per database level? Splitting up mem optimized tables accross db's on standard edition could then be a viable strategy (if you have memory to spare)?
    – Randi Vertongen
    Apr 25, 2019 at 17:36
  • We do not have memory optimized tables.
    – Alex Bochkov
    Apr 25, 2019 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.