SQL Server: Memory Usage High (7GB) + Page File Usage High (7GB) but CPU Usage (2% to10%) [duplicate]

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

Solution 1:

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.