Hi colleagues

My instance of SQL Server 2012 has SP2 with CU5 update on a Hyper-V VM with Dyamic Memory.

I have many data warehouse (databases) within this instance and one in particular consumes most of the buffer pool through a large index leaving the other databases with a single page each in the buffer?

So in effect this one database hogs the buffer leaving less than 1% for the rest of the databases?

I am constantly getting low PLE on this server and wonder why SQL does not just ask for more RAM since the Server is using Dynamic Memory.

What can I do to allow the Buffer Pool to be shared more efficiently?

Thanks in advance.

Dominic

What can I do to allow the Buffer Pool to be shared more efficiently?

Thanks in advance.

You cannot this would be taken care by SQL Server itself. The reason why one of the database is present completely in memory and others don't get much of the share is because the tables regarding the database are accessed widely and so they are in memory. For other database there tables are accessed not so frequently leaving them on disk.

If sql server start accessing other database heavily you would see there share increasing in buffer pool. This is something not to worry about IF YOU HAVE SUFFICIENT RAM

I have 200GB RAM available in Dyamic Memory but only 50GB ever gets used. Why does not SQL request all of the RAM and so put all the tables in memory?

I have 200GB RAM available in Dyamic Memory but only 50GB ever gets used. Why does not SQL request all of the RAM and so put all the tables in memory?

Please dont use dynamic memory as a term to refer to SQL Server memory. What is value of max server memory what is output of below query

select * from sys.dm_os_process_memory

Are you saying 200G is RAM assigned to virtual machine or SQl Server?

Does sql server service accoutn has privilege 'Adjust Memory Quotas for the process'

If you are using dynamic memory in your VM, then perhaps SQL Server never bumps up its memory usage to an acceptable value. And regardless of the value, your DW database apparently happens to get most of the memory in the BP. The later thing can be handled by separating the databases in different instances (Resource Governor might be suggested here, but RG will not help since it doesn't govern the BP).

I have 200GB RAM available in Dyamic Memory but only 50GB ever gets used. Why does not SQL request all of the RAM and so put all the tables in memory?

There's this little thing called Windows in between SQL Server and the hypervisor, if you look at the task monitor or perfmon or something in that Windows instance, does it know that it has 200gb RAM?

It seems a separate issue why whatever memory is used for buffers, would be hogged by one database, how are you determining the buffers being used by different databases, and what is the activity level on those databases?  If it's true they aren't getting any buffers then performance for those db's must be truly awful.  Is that the case?

Josh

Max server memory is set to Maximum value is. No limit.. The 200Gb refers to the maximum amount of RAM available to VM. I will send through other stat soon.

I believe it needs mor RAM but SQL seems stuck not able to ask for more?

Yes task manager shows that the server has a' maximum of 200Gb maximum available but only is using 50Gb of that maximum amount. So the server says that is using 42GB of the 50GB with 8GB RAM free. So the server runs at about 80% usage but never requests any more RAM but also PLE is around 5. So even though the server says it has 8GB free and can request up to a maximum of 200GB the other databases are terribly slow including the main one. I don't understand why SQL does not consume the other 150GB RAM or at least 145GB save 5 or so for the operating system?

The 200GB is the maximum available Dynamic Memory to the VM. SQL has been left at the default MAX setting which is enormously high but never grows above about 42GB RAM? I did not know about this Adjust Quota policy, if the SQL Server Service account does not have this permission how would the RAM grow after reboot of server from say 4GB to the 42GB?

Well, it occurs to me I have no idea how Windows decides how much RAM to grab.  Anyone?

But focusing back on SQL Server, can you get a figure for "Target Server Memory" from perfmon or DMV?

Josh

I would pay close attention to Hyper-V's dynamic memory functionality. It is apparent (from the thread) that SQL Server doesn't grab more than some 40-ish GB memory. Why? Well, how is Startup Memory and Memory Weight configured in Hyper-V for this machine? For instance, you can't grow beyond 16*Startup Memory. So if Startup Memory is 3GB, then it cannot grow the Buffer Pool beyond 48 GB, etc. And similar regarding Memory Weight - how is this configured compared to other machines?

Here's a good one:

https://msdn.microsoft.com/en-us/library/hh372970.aspx

So even though my server has max available RAM of 200GB if the startup RAM is only 3GB then the buffer pool will never grow beyond 48GB for SQL? Is this documented anywhere other than the document you linked which talks about SQL Server 2008 and below in a SQL 2012 document.Thanks for your great help.

• Edited by dloren01 18 hours 8 minutes ago

Yes, according to that document. Note that this setting is in Hyper-V - not SQL Server. I'm no Hyper-V expert, but that description pretty much matches what you see...

Wow ok, no wonder my SQL servers have been crying... thanks Tibor. till next time.