Solved

sql server dynamic memory?

Posted on 2016-07-26
3
87 Views
Last Modified: 2016-08-08
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d882c47e-7988-4f2b-833b-3c3b207ee27e/sql-server-memory-dynamic-or-fixed?forum=sqldatabaseengine

is the dynamic memory concept for sql server or for the underlying storage vm system, as in reference in the above article?

Min/Max memory does not have anything to do with the 'dynamic' concept mentioned in the article, does it?
0
Comment
Question by:25112
3 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 250 total points
ID: 41730078
I'm not sure about your question. Especially the VM point.

SQL Server needs memory for its operations. It uses the most of it for the buffer pool, where it stores data pages for faster access. Cause reading pages from memory is magnitudes faster then reading the same page from disk.

Thus when you don't set a maximum value, then SQL Server will allocate as much memory when needed. After that, this memory is only relased, when the OS signals to do so.

This has three performance drawbacks:
1. The OS process which needs the memory, needs to wait until the memory is relased.
2. After relasing the memory, the pages in the buffer pool may be no longer optimal for the workload, so additional reads from disk may happen.
3. SQL Server will try in future to request additional memory from the OS before reading pages. Whether this memory is granted or not, SQL Server has to wait for that request to finish before it can continue to process which initialized that request.

Also keep in mind, that SQL Server uses the buffer pool to store the most used pages in memory.

So as a conclusion: Using a good choosen min and max memory boundary values will reduce those requests and leave enough memory for the OS processes. To choose this values just take a look at that database size. Often entire databases fit into memory, so a higher lower value makes sense to reduce SQL Server memory requests. And set the upper value to leave enough memory for the OS, but plenty for optional SQL Server usage.
btw, it's often recommmended not run other processes on the same host.

Now for the case that you're running SQL Server in a VM. Modern VM hosts detect whether memory is accessed. So even when SQL Server has allocated its maximum amount of memory in the VM guest, the host can page it to disk. So when these pages are really rarley used, other VMs can use the hosts physical memory.

This is also the explanation for seen scenarios where SQL Server has allocated enough memory to store the data in memory, but the latency of SQL Server looks like it reads from disk. Cause the VM host is indeed doing that.

On measure showing you whether SQL Server needs more or less memory is the standard report Memory Consuption:

Untitled.png
The value of Page Life Expectancy is a good estimate of how long a data page stays in memory (average). High values indicate that SQL Server has enough memory. Low values smaller than 1000 tell you that SQL Server has not enough memory allocated from the OS.

See also Page Life Expectancy and Memory bottleneck if….
0
 
LVL 12

Assisted Solution

by:funwithdotnet
funwithdotnet earned 250 total points
ID: 41730623
That was a nice, comprehensive reply from ste5an.  Thank you ste5an.

Author:
It has nothing to do with VM space. VM space is for the virtual machine that runs the OS that runs SQL Server. Total machine RAM.

I use a pretty simple method. I have dedicated SQL Servers that house many databases. I want SQL Server to have as much memory as practically possible - I really want best performance. I set the VM for 16GB (for example) and use Windows Server Standard as the host OS.

SQL Server memory management is always dynamic.  Setting min & max values only limit the dynamic range. For SQL Server, I leave min at 0 and set the max to 2 GB below the total machine memory (14 GB -14336 MB) to allow room for a fat OS, even though there is usually at least one GB of under-utilized RAM. Sometimes, some SQL jobs cause the OS to need a little extra memory. That's what the extra one GB is for.

Once the host is running SQL Server, I expect to see that SQL Server is using 14 GB of RAM and a little over one GB free. The SQL usage allocated space, it's not necessarily being used. It will be completely used before a few hours pass, but the SQL memory utilization remains constant relative to the OS.

Good luck!
0
 
LVL 5

Author Comment

by:25112
ID: 41747858
helped- thanks.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Veeam Backup & Replication has added a new integration – Veeam Backup for Microsoft Office 365.  In this blog, we will discuss how you can benefit from Office 365 email backup with the Veeam’s new product and try to shed some light on the needs and …
Gift cards are not a new concept - it's been around for a very long time.  Undoubtedly, over the past you have received such a card or purchased one for a friend or relative.  Are you aware that you've been feeding the machine?  If not, read on :)
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Notifications on Experts Exchange help you keep track of your activity and updates in one place. Watch this video to learn how to use them on the site to quickly access the content that matters to you.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now