April 2, 2010 at 9:09 am
From what I read Target server memory is how much SQL Server is willing to have. Total is how it currently has. If Total is higher than Target, then SQL Server is under memory pressure (according to what I read).
But it seems to me that it should be the opposite: if Total is less than Target (SQL Server has less that it would like to) it is under memory pressure (because for some reason it cannot aquire what it is
willing to).
Can someone explain this to me please?
April 2, 2010 at 9:31 am
Not sure where you read but you are correct Target is how much memory SQL would like and Total is how much it has. I'm not sure you would ever see Total higher than Target.
SQL Server 2008 Internals and Troubleshooting[/url]
Even though this book is for SQL 2008 most of it can be applied to 2005 as well. This information comes out of this book. Christian Bolton [Blog [/url]] has written a good deal about SQL Server memory and you can find web cast he has done very useful as well.
Target Server Memory indicates the ideal size for the buffer pool. Total and Target should be almost the same on a server with no memory pressure...If Total is significantly less than Target then it's likely that SQL Server cannot grow the buffer pool due to memory pressure...
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 3, 2010 at 7:43 am
sp_lena1225 (4/2/2010)
From what I read Target server memory is how much SQL Server is willing to have. Total is how it currently has.
Just the Buffer Pool, but yes.
If Total is higher than Target, then SQL Server is under memory pressure (according to what I read).
True. If SQL Server receives a Low Memory Notification from Windows, or notices excessive system paging activity, it may reduce its target buffer pool size and/or signal other internal memory brokers to trim their caches to free up memory.
But it seems to me that it should be the opposite: if Total is less than Target (SQL Server has less that it would like to) it is under memory pressure (because for some reason it cannot aquire what it is willing to.
SQL Server continually adjusts its target. You can see whether each broker was last asked to Grow its cache, keep it Stable, or Shrink by looking at the sys.dm_os_memory_brokers DMV. Total may be less than Target simply because SQL Server has not needed the memory yet.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply