Not the usual SQL memory usage question (Hopefully)
Hi all,
I have an interesting situation which I was hoping someone could help with or provide some ideas for new areas of investigation. I have 2 instances of SQL server one 2008 x64 standard SP2 running on Windows 2008 on a Vsphere4 cluster, the other is 2003 x64 and SQL 2008 x64 physical server. They have 8GB of RAM. The servers are exhibiting what I think is really unusual behavior but am looking for verification. In short the system is reporting (through perfmon, taskman and processXP) that every ounce of available physical memory is being used and around 80% of the page is being used. This usage is constant and I have never seen this, typically in my experience memory would fluctuate as OS requests come in and SQL hands RAM back to the OS. ProcessXP shows that SQLserver has 8gb VM but 200mb Private byte allocation which goes up to 2GB under load. 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.
I have an interesting situation which I was hoping someone could help with or provide some ideas for new areas of investigation. I have 2 instances of SQL server one 2008 x64 standard SP2 running on Windows 2008 on a Vsphere4 cluster, the other is 2003 x64 and SQL 2008 x64 physical server. They have 8GB of RAM. The servers are exhibiting what I think is really unusual behavior but am looking for verification. In short the system is reporting (through perfmon, taskman and processXP) that every ounce of available physical memory is being used and around 80% of the page is being used. This usage is constant and I have never seen this, typically in my experience memory would fluctuate as OS requests come in and SQL hands RAM back to the OS. ProcessXP shows that SQLserver has 8gb VM but 200mb Private byte allocation which goes up to 2GB under load. 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
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
