September 11, 2010 at 1:20 pm
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
September 11, 2010 at 1:25 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2010 at 2:05 pm
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
September 13, 2010 at 2:11 pm
Yes, Instance 4 appears to be experiencing Mmeory Pressure.
Thank You,
Best Regards,
SQLBuddy
September 13, 2010 at 2:49 pm
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
September 13, 2010 at 3:05 pm
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
September 13, 2010 at 3:20 pm
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
September 13, 2010 at 3:27 pm
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
Thank You,
Best Regards,
SQL Buddy
September 13, 2010 at 3:27 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2011 at 2:32 pm
so who to believe?
March 9, 2011 at 7:51 am
Right, I'm so confused now and I thought I had it figured out!
April 14, 2011 at 1:16 pm
sqlbuddy123 (9/13/2010)
I think this is correctTotal 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
Thank You,
Best Regards,
SQL Buddy
I do not agree with this. In my case with the same methodology which you have up behaves exactly the opposite way. I believe if total < target then memory pressure.
April 14, 2011 at 1:44 pm
It's not this simple. The more I dig into things, the less I think I understand on memory pressure and the many ways to detect it.
First, if total (what you have) < (target), the instance does not have enough memory. It wants more, and can't get it for some reason.
If total > target, as Gail mentioned, you have some potential issue.
When I read the memory bottleneck section in here: http://technet.microsoft.com/en-us/library/cc966540.aspx#EGAA, I see no mention of target or total memory, but a detailed explanation of various types of memory pressure. I also see lots of references to DBCC MemoryStatus, which is explained here, but very confusing:
July 8, 2011 at 12:39 pm
Was reading up on this and thought I'd add some insight. We just spun up a new SQL server that will grow immensely, but right now it's barely touched. We put in 64GB memory and set max memory to be 32GB. On all our similar servers, Target ALWAYS = max memory. On this server, the same is true, but Total Memory is only 1GB.
So a few things are true:
Target means "max memory" if the value is set.
If "max memory" is not set it means what SQL thinks it needs (based on some calculation)
Total Memory means how much memory SQL is using. On all our seasoned SQL server, Total = Target meaning it's "possible" if we raised max memory target and subsequently Total memory would go up.
Finally, if Total < Target, then there is enough memory. There's no way our server which has almost zero activity is saying there's memory pressure and it wishes there were more memory than 1GB, because there is! If Total=Target, there may be memory pressure. I don't see how Total could ever get higher than target. That'd be like saying SQL only wanted so much or was allowed so much but SQL ignored itself and took more!
Point being if Total=Target, does not necessarily mean things are peachy. You need to turn off Max Memory, let it be dynamic and then see how Target and Total adjust. I'm almost certain if I did this on my servers I would see Target far exceed the 32GB we allow it to take normally and probably Total would grow as the OS and apps let it take memory.
July 8, 2011 at 5:10 pm
Good post Thats more or less the conclusion i came to in the months since i first posted here. looking at the original post there was confusion as to whether total memory being less than target memory represented pressure. This is counterintuitive if you assume target is the max the server can use and total is what ther server is acually using. but the key word is "significantly" . "If Total is significantly less than Target,then it’s likely that SQL Server cannot grow the buffer pool due to memory pressure" so in the example if the otal memory never grew more than 1 gig and the server was running for awhile, that could indicate memory pressure just as much as could total=target. Figuring it out whether or not there is actual memory pressure based on these indicators is the reason they have DBA's i suppose, but I think there could be a better way to represent it.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply