Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Manage server memory via Resource Governor/WSRM vs SQL server Properties Expand / Collapse
Author
Message
Posted Sunday, July 7, 2013 7:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 7, 2014 4:09 AM
Points: 9, Visits: 58
Hi,

I am very new to dba and I have question about managing min/max server memory.

What would be the reason of configuring server memory via Resource Governor instead of via SQL properties page? What is the benefit over each other?

However, it does make sense though when configuring the CPU consumption via Resource Governor as Properties page does not offer the functionality.

Can anyone please shed a light, will be much appreciated.

Thanks.
Post #1471012
Posted Monday, July 8, 2013 1:37 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:08 AM
Points: 4,427, Visits: 4,171
What would be the reason of configuring server memory via Resource Governor instead of via SQL properties page? What is the benefit over each other?


Configuring the max\min server memory is for the whole instance, while resource governor is used to limit user connections. You may want to limit the max server memory when you have more than one instance or in case that other applications are running on the same machine.
Resource governor is useful when you want to limit the resource used by certain user connections g.e. a reporting system.

Also remember the percentages you assign through resource govenor are a percentage of the max memory, not the physical memory in the machine.


Markus Bohse
Post #1471063
Posted Monday, July 8, 2013 4:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 7, 2014 4:09 AM
Points: 9, Visits: 58
Thanks Markus, however I am still not clear.

When configuring max memory from Resource Governor, we specify the max percentage in resource pool. Definition of resource pool taken from msdn website is, it represents a subset of physical resources available to DB Engine instance. So, I think it's the opposite of your explanation that Resource Governor percentage is not from physical memory.

Can you please clarify further.

Thanks
Post #1471110
Posted Tuesday, July 9, 2013 4:00 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:08 AM
Points: 4,427, Visits: 4,171
Sorry If I wasn't clear enough but what I meant is this:
c4ntaloop (7/8/2013)
Definition of resource pool taken from msdn website is, it represents a subset of physical resources available to DB Engine instance.

That means it's a subset of the resources you defined by setting the max memory for the instance (plus some other settings like CPU affinity.)

When I wrote that's not the same as the physical memory, I actually meant the available RAM in the machine.
Let's say you have a server with 32Gb of RAM and you set the max memory for the instance to 25GB, a 20% RAM limit in Resource Governor would be 5 Gb



Markus Bohse
Post #1471515
Posted Tuesday, July 9, 2013 5:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 7, 2014 4:09 AM
Points: 9, Visits: 58
Ah its making sense now, thanks Markus for explaining. I misunderstood that part.

If I can put in different scenario, if we don't specify the max server memory (leave to default), max percentage in Resource Governor would be the percentage of whole server's physical resources. In the example, that will be 20% of 32Gb. Is that right?

Post #1471541
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse