October 30, 2008 at 1:29 pm
Hi there I hope you might be able to help me out.
http://msdn.microsoft.com/en-us/library/aa173937(SQL.80).aspx --says
Target Server Memory (KB)-Total amount of dynamic memory the server can consume.
others I see this ---SQL Server would like to use to operate efficiently. and
perfmon says -- Total amount of dynamic memory the server is willing to consume which seems to be close to can but not exactly...
total seems to be consistent -- which is currently using
seems to be a big diff from can and would like to use?
so if I have 32 gigs and it says can use(target) is 32 and I am using(total) 30 that seems to be okay. and if it is 32 = 32 then I could have pressure, possibly...I should check more counter, (iBuffer Cache Hit Ratio, Memory: Pages/sec ,ect)
it seems like total can never be bigger then target? you are saying this is possible...sorry they more I read in this area I am getting conflicting answers. I have a read that total = target is okay and that does not make since.
seems like it should be
total = target - possible problems, but then again sql will use all of the memory it can get so maybe not. I see a lot of posting saying this normal
total < target - okay
total > target - not possible...if this is correct. Total amount of dynamic memory the server can consume. since it would not be possible to have using(total) be greater then what can be used
thanks for your help
October 31, 2008 at 2:56 am
As far as I know, total is what it has and target is the value it's aiming for. If target is higher than total, then SQL will be acquiring more memory. If target is lower than total, then SQL will be relinquishing memory.
Target can be lower than total. If the OS is under memory strain then it will signal all applications and ask then to reduce the memory they're using. In this case, SQL will set the target lower and then reduce its memory usage.
If target = total then SQL has all the memory it wants and it's not been asked to reduce the memory allocation, so that is good.
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
October 31, 2008 at 3:41 am
Target memory is calculated on SQL Server startup and will depend on your OS and how much memory there is on the server, it will be something like Total Server Memory - 2-300 MB for the "mem-to-leave-area" of SQL Server (executable, thread stacks and others). As Gail points out, this can be adjusted downwards if we find that AvailableMB in the OS is below a certain threshold.
So on a 32-bit system - default configuration - it will be 1,7 GB, if you add the /3GB switch it will be 2,7GB and so on.
If you set the max server memory setting using sp_configure, your Target will equal this (which I suggest you do to avoid paging, and other, issues related to a starving OS).
If you use AWE on SQL Server 2000, you absolutely have to.
Total server memory is what you currently have committed in the buffer pool. If Total = Target - which it probably does as you have noted - then you would look at other counters to find out if we have hit the roof in a bad way or if we are just hanging up there because we can 🙂
I don't know which versions you are on but since you have 32GB you should probably look into Max and Min server memory settings and the Lock Pages in Memory user right.
HTH!
🙂
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
October 31, 2008 at 7:42 am
Hi Elisabeth,
thanks that clear up more then I have found so far. I am running 64 bit server and sql 2005 which is set at default.
I believe I got a little confused with total, thinking it was available which it is not. seems like this total=target does not tell us a whole lot.....probably help to look at all 3 available,total and target.
I have several problems at the moment so I trying to get them all fixed when I take the server down for maintenance.
I am low on page file. --only set 4 gigs and we are running full text search. microsoft recommends 2,3 * memory....seems like a lot
perfmon readings: paging file %usage 100%....my guess is the target=total could have something to do with each other ..since the buffer cache is staying at 99% it does not seem to have a low amount of memory.
I also have plenty of fragmentation. and Avg. Disk sec/Write and reads are about .24 just starting to cross .20 are a little high..so this seems to go hand and hand.
thanks again for the help
October 31, 2008 at 8:37 am
Hi again,
You need to set your Max server memory setting to something that leaves at least 2-3GB to the operating system (ref: Slava Ok http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx).
If you have concluded that you have an issue with paging, have a look at http://support.microsoft.com/kb/918483 "How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005".
And before you ask 🙂 Min server memory stipulates the amount of memory your Total memory will never go below (once it has reached above it).
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply