October 21, 2008 at 4:49 am
Im getting my memory counters all confused .. And the results Im getting through perfmon make no bloody sense !!!!
Ok if I understand correctly
Avaible bytes is the amount of memory availabe to SQL at that point in time
Working Set is the amount SQL is actual using
Total server memory is the toal amount amount of memory all porcesses (including SQL) on that server are using...
so far so good...
I have (on a 4gb box with 3 gb fixed size set aside for SQL )
available bytes = 1.46gb
working set = 1.65 gb
total server = 1.58 gb
So how is SQL using more memory than all the ohter processes and itself ?! i.e working set > total server
Please help while I have some hair left that is not grey and at least two brain cells left to rub together !
~Simon
October 22, 2008 at 7:20 am
available bytes is total amount of physical memory available to ALL processes on the server. 1.46GB pretty good
total server memory (SQL server:memory manager object) is the amount of memory SQL is using, but this is only the buffer cache, which is why it is less than working set because this also includes memory used by proc cache and locks and user connections etc.
You say you have 3 gb fixed size set aside for SQL, so I presume 3GB switch is set in the boot.ini file. SQL will only be able to use memory above 2 Gb if you are using Enterprise edition.t
---------------------------------------------------------------------
October 22, 2008 at 7:26 am
george .. thank you
its been bugging me for days ....
as for the 3gb set I mean via EM -> SQL Server properties -> used a fixed memory size
oh and yes the EE.
October 22, 2008 at 7:40 am
Simon_L (10/22/2008)
george .. thank youits been bugging me for days ....
as for the 3gb set I mean via EM -> SQL Server properties -> used a fixed memory size
oh and yes the EE.
then I would change that to allocate memory dynamically, especially if SQL is only app on the box. and set the /3Gb switch in boot.ini. Will require a reboot to take effect.
---------------------------------------------------------------------
October 22, 2008 at 7:45 am
Im going to show my ignorance but what is the difference.. ? e.g is it better performace wise etc
I did ask in another post about dynamically allocating vs fixed size and it seemed to be down to preference.. whereas an articile on sqlperformance suggested if it was the only app then set to fixed..
thank you
simon
October 22, 2008 at 8:52 am
SQL is good at managing its own memory so I would not try to second guess it as to how much it needs, or risk throttling the OS because it cannot get enough memory.
This is a subject that can get complex espec. if you have multiple instances, > 4Gb physical memory, AWE enabled, etc, etc. As you have a standard 4GB box (single instance?) I would let SQL manage its memory.
you say you have set it to fixed, but its only actually using 1.5GB ish....................;)
---------------------------------------------------------------------
October 22, 2008 at 8:58 am
george sibbald (10/22/2008)
SQL is good at managing its own memory so I would not try to second guess it as to how much it needs, or risk throttling the OS because it cannot get enough memory.This is a subject that can get complex espec. if you have multiple instances, > 4Gb physical memory, AWE enabled, etc, etc. As you have a standard 4GB box (single instance?) I would let SQL manage its memory.
you say you have set it to fixed, but its only actually using 1.5GB ish....................;)
once again thank you george 🙂
Yes its a single instance 4gb box no AWE etc
As its only using 1.5gb out of the fixed/allocated Im presuming this is a good indicator that whilst 3gb is set aside its actually using far less so why bother setting so much aside (fixed in this instance meaning allocate upto !?)
best regards
~simon
October 22, 2008 at 9:15 am
ahh, allocated up to, now it makes sense, set it to dynamic, may as well.
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply