Max Memory in SQL Server 2016 Standard Edition
What is the memory limit for Standard edition of SQL 2016?
The answer you will most often get is 128 GB. But that answer is not correct, or very partially correct at best case. When you plan a new SQL machine, virtual or phisical, it is really important to know true maximum SQL Standard can use, to properly size the machine if you want to go to the limits.
Take a look at documentation here (2 Applies to SQL Server 2016 SP1):
With a bit of math, if we use Columnstore indexes and have Memory-optimized tables in 2 databases, SQL limit is:
128 + 32 + 2*32 = 224 GB
Stealing is good?
SQL allocations counted into buffer pool limit (like CLR, memory for execution plans, sort&hash, etc.) will STEAL the memory from the buffer pool. We can see exact amount of memory sql allocated through sql memory clerks with this query:
|
1 2 3 4 |
select Memory_MB = (pages_kb + awe_allocated_kb + virtual_memory_committed_kb)/1024, * from sys.dm_os_memory_clerks order by Memory_MB desc |
If we have a big sort operation, we can here observe how it “steals” from the buffer pool, because buffer MEMORYCLERK_SQLBUFFERPOOL clerk will shrink. Everybody “steals” memory from the buffer pool 🙂
Memory outside of the buffer pool
But SQL also allocates memory outside of the buffer pool, outside of that (128+32+N*32) GB limit. Like memory for thread stacks and direct allocations from the OS (extended sp, DLLs, win heap usage, linked server provider allocations). Therefore SQL Server process can, and will, allocate more than that limit.
By the way, “Max Server Memory” corresponds to 128GB limit, it does not include columnstore or memory-optimized tables and indexes.
Other processes on the machine
Along with SQL Server process, there is an OS that takes some RAM, and other processes running on that machine. For example, SQL Server Management Studio, SQL Profiler that someone forgot to stop and close for a few days (accumulationg RAM it consumes), or some other app. Therefore, when you are sizing your machine you must account all of that into equation.
Adjusting the “Max Server Memory”
The best, “fool proof” method I know besides estimation and calculation, is to measure Available memory and Free memory on the server through some time, e.g. using Perfmon counters. Available memory should be always above 500MB, and Free memory should be above 100MB.
To quickly glance at free memory, we can use Task Manager. SQL Can only allocate new memory from “Free” memory, not from available! In this example, we have 9,2-2,2 = 7 GB of free RAM:
But it is better to track available and free memory for a period of time, using perfmon, like this:
Adjust the “Max server memory” down until it is withing mentioned numbers.
If using this method you got “Max Server Memory” below 128GB, you are not limited by Microsoft, but by total RAM of the machine. This method will give other processes and OS to “breathe”, but it might happen that SQL has not enough memory, which also can be measured, but that is for another topic. Or queries are not optimal and they bring large tables into memory by full-scanning them. Compression (page or row) can also help to fit more data into less RAM and stay within limit.
The Buffer Pool Extension (BPE)
A dear colleague William Durkin brought up that BPE is not included into 128GB memory limit of SQL Standard Edition. That is somewhat expected, since BPE is a disk space, not RAM. Therefore, we could setup a low-latency storage, like NVRAM (very fast SSD) and use it for BPE, similar to OS that uses a page file. Or maybe even niftier thing comes to my mind: put a lot of RAM into a machine with SQL Standard Edition, create a RAM Drive, and put BPE to use that ramdrive. It should be much faster than having BPE on NVRAM, since RAM latency is like 1000x faster than fastest SSD, but I have never tried that personally. It would not make much sense if you have Enterprise Edition (no 128GB limit), then you would better of using RAM directly. Any thoughts? Please comment!
Conclusion
When sizing your SQL Server machine, take into account not only 128GB limit, but also memory for columnstore (32GB), memory-optimized (N*32GB), allocations outside the buffer pool, OS allocations, and other processes allocations. Because SQL is capable to utilize way more than 128GB, and that is really a good news because “Standard” limit is not so tight now. And if you plan to push the memory limits, your SQL Server machine should have way more RAM than 128GB. Even in SQL Server standard edition.
What are your thoughts on that? Do you have some other method to calculate memory limit or track SQL memory usage?
Have you considered the effects of Buffer Pool Extension on this investigation?
Thanks William for this. I still remember your brilliant session from one of SQL Saturday’s. Will update the post to include the BPE info. Thanks again!