I have added perfmon counters for SQL memory (which is consistent with ProcessXP) Buffers (100%) and the usual OS and hardware suspects. Nothing is claiming to own the memory which is being used and I can find no trace of it's allocation. Now the fun parts; SQL is set to use a max of 2048 mb and seems to be respecting this as the max private bytes in processxp is 2gig. The second interesting thing is that periodically (no scheduled tasks or obvious reason) whatever has this memory gives it back to the OS and we see only SQL in use + OS threads.
The boxes in question are dedicated to SQL server. They are both running the same version of the database, other database servers running different databases appear to behave as I would expect, heavy RAM usage certainly but not to this extent.
My question is whether I am missing something really obvious here or if we should be looking for a memory leak. I am far from a DBA but have a fair amount of experience supporting these environments.
Thanks in advance.
SQL Server 2005 or above 64 bit is a bad consumer of memory(RAM) ,buffer memory is the highest memory consumer ,to check run DBCC MEMORYSTATUS for more memry related details.
use perfmon to check the available MBytes, if it is less than 10% of Installed Memory, then u have a memory leak
answered Aug 08, 2012 at 10:14 AM
as a tool, to backup perfmon, for checking system performance I use processexplorer from sysinternals (http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx) I use it on all my machines to see what is going on. You can actually identify and associate threads to spids with it if you need to.
I'd suggest installing and seeing how it describes your memory usage