x

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.
more ▼

asked Aug 01, 2012 at 05:46 PM in Default

evio101 gravatar image

evio101
10 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Aug 08, 2012 at 10:14 AM

ramesh 1 gravatar image

ramesh 1
2.2k 63 67 69

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Aug 08, 2012 at 10:39 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x728
x57
x3

asked: Aug 01, 2012 at 05:46 PM

Seen: 943 times

Last Updated: Aug 08, 2012 at 10:40 AM