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 1234»»»

Total Server Memory Vs Target Server Memory Expand / Collapse
Author
Message
Posted Saturday, September 11, 2010 1:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 24, 2011 10:56 PM
Points: 215, Visits: 679
Hi,

We have SQL Server 2005 EE x64 with SP3.

RAM = 16 GB
Max Server Memory = 12 GB
Min Server Memory = 0 default value
Lock pages is enabled

In our server, Total Server Memory (KB) and Target Server Memory (KB) are always same.

I read in many forums that if both Total Server Memory (KB) and Target Server Memory (KB) are same, then you have Memory Pressure.

You should have Total Server Memory (KB) less than the Target Server Memory (KB) and that indicates NO memory pressure

But today I read the book "professional-sql-server-2008-internals-and-troubleshooting" and from that:

MSSQL$<instance >:Memory Manager\Total Server Memory (KB): This indicates the current
size of the buffer pool.

MSSQL$<instance >:Memory Manager\Target Server Memory (KB): This indicates the ideal
size for the buffer pool. Total and Target should be almost the same on a server with no
memory pressure that has been running for a while. If Total is significantly less than Target,
then it’s likely that SQL Server cannot grow the buffer pool due to memory pressure, in
which case you can investigate further


So it's the other way.i.e

If you have Total Server Memory (KB) and Target Server Memory (KB) are always same ---> NO memory pressure

If you have Total Server Memory (KB) less than the Target Server Memory (KB)---> Memory pressure


I appreciate your inputs and eliminate the confusion by clarifying which one is correct and why

Thanks
Post #984311
Posted Saturday, September 11, 2010 1:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 39,980, Visits: 36,347
Total is what SQL currently has. Target is what it thinks it wants under the current load. So if total is lower than target, either SQL is still building up the memory allocation, or it needs more than it has (ie it's under memory pressure)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #984315
Posted Saturday, September 11, 2010 2:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 24, 2011 10:56 PM
Points: 215, Visits: 679
Thanks Gail,

On Server A, we have 4 instances. We have 16 GB RAM. Lock pages is enabled.

For each instance, I have set the Max Memory to 3 GB and Min Memory to default and left 4 GB for OS

For 1st 3 instances, the Total Sever memory = Target Sevrer Memory.

But the last instance has the Tatoal Server memory always has 2 Gb and Target Server memory has 3 GB.

In this case, can we say that the instance 4 is under memory pressure?

Thanks
Post #984320
Posted Monday, September 13, 2010 2:11 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:50 PM
Points: 1,194, Visits: 2,220
Yes, Instance 4 appears to be experiencing Mmeory Pressure.

Thank You,

Best Regards,
SQLBuddy
Post #985077
Posted Monday, September 13, 2010 2:49 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 24, 2011 10:56 PM
Points: 215, Visits: 679
Yes, Instance 4 appears to be experiencing Memory Pressure.


In this case for instance 4, when I set the Max Memory to say 4GB, then if the Total Server memory is going to increase to 4 GB then we can say that there is NO Memory pressure?

What if the Total Server memory is still 2 GB even after increasing the Max Memory to 4GB? Then the Memory Pressure still there?

If the Max Memory is set 3 GB and the Total Server memory is 2 GB, then I'm assuming that the instance is happy with 2 GB and it has 1 GB extra memory . Is that right?

Thanks
Post #985089
Posted Monday, September 13, 2010 3:05 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:50 PM
Points: 1,194, Visits: 2,220
You are right. Sorry for my previous post. I got little confused.

So here is the deal..

If Total Memory >= Target Memory ------> Represents Memory Pressure
Total Memory < Target Memory ------> Represents No pressure.

So Instance 4 is not experiencing any mem pressures. But the other 3 are experiencing Mem Pressure.

Thank You,

Best Regards,
SQLBuddy
Post #985094
Posted Monday, September 13, 2010 3:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 24, 2011 10:56 PM
Points: 215, Visits: 679
If Total Memory >= Target Memory ------> Represents Memory Pressure
Total Memory < Target Memory ------> Represents No pressure.

So Instance 4 is not experiencing any mem pressures. But the other 3 are experiencing Mem Pressure.


But if you read the book "professional-sql-server-2008-internals-and-troubleshooting"

The memory pressure is explained the other way..from the book


MSSQL$<instance >:Memory Manager\Total Server Memory (KB): This indicates the current
size of the buffer pool.

MSSQL$<instance >:Memory Manager\Target Server Memory (KB): This indicates the ideal
size for the buffer pool. Total and Target should be almost the same on a server with no
memory pressure that has been running for a while. If Total is significantly less than Target,
then it’s likely that SQL Server cannot grow the buffer pool due to memory pressure, in
which case you can investigate further



So I just want to know which one is correct

Thanks


Post #985101
Posted Monday, September 13, 2010 3:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 39,980, Visits: 36,347
sqlbuddy123 (9/13/2010)
So here is the deal..

If Total Memory >= Target Memory ------> Represents Memory Pressure


If Total > Target it means that SQL is trimming its working set due to a request from the OS. It can signify that the OS is under memory pressure, but not SQL.

Total > Target means that SQL is reducing its memory usage. It's not going to reduce its memory usage if it is under memory pressure. In fact, it won't reduce its memory usage unless either the OS tells it to or the max server memory setting is changed.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #985106
Posted Monday, September 13, 2010 3:27 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:50 PM
Points: 1,194, Visits: 2,220
I think this is correct

Total Memory >= Target Memory ------> Represents Memory Pressure
Total Memory < Target Memory ------> Represents No pressure.


http://www.sql-server-performance.com/tips/performance_monitor_memory_counter_p1.aspx

http://blogs.msdn.com/b/teekamg/archive/2007/11/06/sql-server-memory-related-performance-counters.aspx

Thank You,

Best Regards,
SQL Buddy
Post #985107
Posted Tuesday, March 8, 2011 2:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:24 AM
Points: 6, Visits: 132
so who to believe?
Post #1075152
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse