Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » Total Server Memory Vs Target Server Memory
Add to briefcase
38 posts, Page 1 of 4
1234»»»

Total Server Memory Vs Target Server Memory Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
pshaship
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
GilaMonster
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:11 PM
Points: 37,741, Visits: 30,020
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
pshaship
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
sqlbuddy123
Posted Monday, September 13, 2010 2:11 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 3:17 PM
Points: 715, Visits: 1,524
Yes, Instance 4 appears to be experiencing Mmeory Pressure.

Thank You,

Best Regards,
SQLBuddy
Post #985077
pshaship
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
sqlbuddy123
Posted Monday, September 13, 2010 3:05 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 3:17 PM
Points: 715, Visits: 1,524
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
pshaship
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
GilaMonster
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:11 PM
Points: 37,741, Visits: 30,020
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
sqlbuddy123
Posted Monday, September 13, 2010 3:27 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 3:17 PM
Points: 715, Visits: 1,524
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
mcroce
Posted Tuesday, March 08, 2011 2:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 7:07 AM
Points: 6, Visits: 119
so who to believe?
Post #1075152
« Prev Topic | Next Topic »

Add to briefcase
38 posts, Page 1 of 4
1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.