Solved

sql server dynamic memory?

Posted on 2016-07-26
3
75 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 32

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installat…
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
This video shows you how to use a vSphere client to connect to your ESX host as the root user. Demonstrates the basic connection of bypassing certification set up. Demonstrates how to access the traditional view to begin managing your virtual mac…
This video shows you how easy it is to boot from ISO images for virtual machines with the ISO images stored on a local datastore on the ESXi host.

758 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

23 Experts available now in Live!

Get 1:1 Help Now