Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Possible Duplicate:
SQL Server memory constantly going up, from 1GB RAM used to 7GB in a week.

Hello all,

I am running SQL server with a lot of scripts that update large amounts of data (millions of records on several tables). However, I think there is a memory leak as RAM usage goes to 7.5GB of the possible 8GB - this is on a 8 Core Server. The Page File goes to 7.28GB as I think SQL server keeps hogging RAM that I don't think its using.

I really need advice on what can be causing this or how I can troubleshoot this!

My Setup

Microsoft Windows Server 2003
Standard 64-bit edition
Service Pack 2

SQL Server 2005
Microsoft SQL Server Management Studio  9.00.4035.00
Microsoft Analysis Services Client Tools 2005.090.4035.00
Operating System 5.2.3790

Application and Usage of SQL Server

I am using SQL server with PHp where I run the scripts and queries via SQLCMD using PHP's exec function.

What can be the possible causes of all this RAM usage and huge Page File?

I hope I have given enough information to try and identify the problem. Let me know if anything else that I should add or try to find out.

Thanks all

share|improve this question

migrated from stackoverflow.com Nov 19 '09 at 14:59

This question came from our site for professional and enthusiast programmers.

marked as duplicate by Mark Henderson Mar 15 '11 at 23:56

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3 Answers 3

up vote 13 down vote accepted

Sql Server memory management is a very broad topic, but there are some things mentioned here that should be addressed.

I'm going to assume you are looking at Task Manager to get what you are referring to as the Page File Usage. This is NOT the size of your physical page file or the amount of physical page file space used - this is what is referred to as the commit charge, which is basically the potential amount of total physical page file space that would be required to hold all VAS (VAS and physical memory are not the same thing). If you want to see the actual page file usage, view the PagingFile:%Usage perfmon counter - it will almost certainly be a very small percentage. The total size of your commit charge (i.e. what Task Manager will display as PF Usage) is basically the sum of all possible VAS (i.e. all page file space + total physical memory on the system).

Sql Server does NOT override the memory management facilities of the OS - it uses the same windows-provided memory APIs as any user-mode application (primarily the VirtualAlloc family and the AWE family, which still is used on 64bit systems), . Sql Server does indeed have a subsystem dedicated to managing it's own memory utilization, however this subsystem does nothing special that isn't available to any user-mode application in terms of requesting, receiving, committing, etc. memory from the underlying OS.

Like others have mentioned, Sql Server is definitely designed to use as much memory as possible based on system/user-defined constraints. However, it will grow into this memory footprint based on the utilization of the system (i.e. it will reserve a relatively small amount of memory at startup and continue requesting/reserving from the OS as it needs). It is also designed to (and fairly good at) respond to OS memory pressure as necessary, and to also keep it's reserved memory footprint until/unless the OS "requests" it back (for a more complete understanding of this, see this blog and this blog and this blog).

If you want to force Sql Server to release/shrink it's current memory footprint, you can simply tell it to do so (assuming you are running Sql 2005 and above) by lowering the value specified in the "max server memory" system configuration and running a reconfigure. There are certainly limitations to this (i.e. this configuration only limits the buffer pool, however this is by far the largest memory consumer). This is NOT a connection-level setting as alluded to elsewhere, it is a system-wide setting (There are memory-related connection-level settings, however nothing that would have anything to do with this question). If you are running Sql 2000, you'll need to make the adjustment and then recycle the Sql Service - NOTE that on Sql 2000 memory management was much, much different in almost every way (and would likely require a separate question/discussion).

I would strongly encourage you to read through a couple of blogs to get an understanding for basic windows and sql server memory management, shouldn't take too long and is well worth the knowledge when trying to understand what is going on in situations like this.

share|improve this answer
    
Excellent response. +1 for you. –  user3914 Nov 19 '09 at 16:06
    
+1 for teh GWC! –  Jason Nov 19 '09 at 17:26
    
This chadhoc guy really knows is memory management..He is the only guy I trust with my SQL servers... –  Thomas Denton Nov 19 '09 at 17:31

SQLServer has its own memory management, it overrides the memory management of the OS. You can constrain the amount of memory it grabs, and there are several ways to do this, but probably the easiest is to start up SqlServer Management Studio, right click on the sql instance, select properties, then go to the memory tab. You can also do this at the connection level if you are calling in to the database frommanaged code.

share|improve this answer
    
Constraining its memory usage is that a solution or do you think there is something deeper that is wrong with our sql server setup/config. –  Abs Nov 19 '09 at 13:22
    
Like Randolph said, it's meant to do that. If you do some meaty queries, then SQL will grab the memory. I wouldn't be too concerned about the page file size, you can also limit that, and it could be just a byproduct of SQL using the RAM so other processes on the server will be consigned to using the pagefile. –  slugster Nov 19 '09 at 13:29
    
Though Sql Server does have it's own subsystem to manage it's own memory footprint and allocation, it does absolutely nothing ever to override any of the memory management facilities of the OS and uses standard Windows APIs for requesting/managing/releasing/etc. any virtual memory. Basically makes use of standard APIs available to any and all user-mode windows applications. –  chadhoc Nov 19 '09 at 14:50
    
Also, not sure what you are referring to in terms of connection-level settings from managed code? Perhaps you mean you can simply run the TSQL statement to actually set the memory limits? Naturally, you wouldn't want to do this for each connection, just not clear as to what you mean by that? –  chadhoc Nov 19 '09 at 14:51

SQL Server is designed to use all the available system memory. As slugster suggests, you can constrain this if you have other things that need RAM on the box, but a general rule is to be grateful SQL is using the RAM :-).

share|improve this answer
    
I know SQL Server is designed to use as much memory as it needs but what about the paging? How do I get it to release memory (set an upper memory limit so that it comes down when not using it?) when it doesn't need it so that memory usage can do down as well as paging? –  Abs Nov 19 '09 at 13:32
    
chadhoc has answered your question, I think. –  user3914 Nov 19 '09 at 16:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.