SQL 2012 Dynamic Memory

Question

SQL 2012 Dynamic Memory

Kevin Stride

SSC Veteran

Points: 240
More actions
March 2, 2015 at 3:14 pm

#294950

Does anyone know if Dynamic memory can be used for SQL 2012 on a Hyper-V 2012 VM?
There seems to be conflicting views of this, some say only static memory can be used or performance would be affected?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Dynamic memory can be used with no issues. It just depends what you're after.

I use it on some virtual machines and it works fine. Make sure you grant the lock pages in memory privilege to the service account to avoid memory trimming.

Obviously, overcommitting memory always has a performance penalty: it just depends how much you're overcommitting.

-- Gianluca Sartori

Kevin Stride SSC Veteran Points: 240 More actions · Answer 2

Hi,

Thanks for your reply.

With regards to the grant lock pages in memory privilege. Is this through the Server Local policy editor? Is this essential to do if using Dynamic memory?

When you say overcommitting memory. Do you mean on startup or the maximum limit? We currently are using 20gb startup and 64gb as the max.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You can grant the security policies using the mmc (start, run "secpol.msc").

It is recommended by the H-V documentation (and in general is a good idea).

Overcommitting memory means assigning to virtual machines more memory than you have in the physical host.

-- Gianluca Sartori

Kevin Stride SSC Veteran Points: 240 More actions · Answer 4

Thanks for the reply.

Is granting the lock pages in memory considered best practice for Hyper-V SQL 2012?

Is there any documentation available on this?

Many Thanks

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

It is in general.

Here's a link to the whitepaper: https://msdn.microsoft.com/en-us/library/hh372970.aspx

-- Gianluca Sartori

sql-lover SSCoach Points: 18530 More actions · Answer 6

Kevin Stride (3/4/2015)
Thanks for the reply.
Is granting the lock pages in memory considered best practice for Hyper-V SQL 2012?
Is there any documentation available on this?
Many Thanks

Watchout!

I also use lock pages in memory (VMware) but you need to make sure there is a reserved memory for the virtual machine. That's the min RAM that the VM will use when it starts and its "pinned". I don't know how that can be set on Hyper V but I am assuming there is a similar setting.

If you set lock pages in memory but the virtual machine does not have a memory reservation the balloon driver will take over if RAM is needed for other VMs and the performance will suffer. The lock pages in memory , in this situation, may affect performance.

Again, not sure about Hyper V, but the lock pages in memory should be carefully evaluated when SQL is running on VMware or Hyper V. I would not worry too much about enabling lock pages in memory for a bare metal machine if its 64 bit and runs SQL2012.

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

sql-lover (3/4/2015)
Kevin Stride (3/4/2015)
Thanks for the reply.
Is granting the lock pages in memory considered best practice for Hyper-V SQL 2012?
Is there any documentation available on this?
Many Thanks
Watchout!
I also use lock pages in memory (VMware) but you need to make sure there is a reserved memory for the virtual machine. That's the min RAM that the VM will use when it starts and its "pinned". I don't know how that can be set on Hyper V but I am assuming there is a similar setting.
If you set lock pages in memory but the virtual machine does not have a memory reservation the balloon driver will take over if RAM is needed for other VMs and the performance will suffer. The lock pages in memory , in this situation, may affect performance.
Again, not sure about Hyper V, but the lock pages in memory should be carefully evaluated when SQL is running on VMware or Hyper V. I would not worry too much about enabling lock pages in memory for a bare metal machine if its 64 bit and runs SQL2012.

Good point. Hyper-V has a "minimun memory" setting that takes care of it. The whitepaper addresses your concerns, if I recall correctly.

-- Gianluca Sartori

Kevin Stride SSC Veteran Points: 240 More actions · Answer 8

Is the best config to set the min and max memory with Hyper-V and then set a slightly lower upper limit within SQL Server? Ie

Hyper-V

Startup mem - 4096mb

min mem - 1024mb

max mem - 32768mb

SQL

Max memory - 30720mb

sql-lover SSCoach Points: 18530 More actions · Answer 9

Kevin Stride (3/4/2015)
Is the best config to set the min and max memory with Hyper-V and then set a slightly lower upper limit within SQL Server? Ie
Hyper-V
Startup mem - 4096mb
min mem - 1024mb
max mem - 32768mb
SQL
Max memory - 30720mb

Well, you don't want to configure max RAM level on SQL equal or close to the Os. You need to give Windows some room as well. If you give all available RAM to SQL, it will take, trust me. And things will get ugly.

Here is a guideline (non buffer pool area or what you leave to the Os)

-2GB for Windows

-xGB for SQL server worker threads

-1GB for miscellaneous

-1 to 3GB for other apps, like backups, linked servers, etc

Remaining one, to SQL server, upper limit.