Home / server / Questions / 236683
Accepted
Danilo Brambilla
Asked: 2011-02-18 03:36:13 +0800 CST

SQL 2008 Memory Usage

  • 772

I have a SQL Server 2008 (ver 10.0.1600) running on a Windows Server 2008 R2 Enterprise server with 8 GB of physical ram. If I open Task Manager I can see on 'Physical Memory' section of 'Performance' tab that only 340 MB are Available of 8191 Total, but I can't see any process using such amount of memory. Please note SQL Server is memory limited to 6GB (Maximum Server Memory = 6000).

If I open Sysinternals Process Explorer, I can see sqlsrvr.exe process has:

Private Bytes:  227.000 K
Working Set:    140.000 K
Virtual Size: 8.762.000 K

What does this means? Is there any way to free up this memory for other process? Why Virtual Size figure as allocated memory? I thought that Virtual Size was 'reserved memory' only.

sql-server-2008 memory-usage

2 Answers

  1. Best Answer

    This is normal. SQL server will always use all available memory, and scale back when the system needs more resources.

    THIS article describes what is going on.

    When you start SQL Server, SQL Server memory usage may continue to steadily increase and not decrease, even when activity on the server is low. Additionally, the Task Manager and Performance Monitor may show that the physical memory available on the computer is steadily decreasing until the available memory is between 4 to 10 MB.

    This behavior alone does not indicate a memory leak. This behavior is normal and is an intended behavior of the SQL Server buffer pool.

    By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache) depending on the physical memory load reported by the operating system. As long as enough memory is available to prevent paging (between 4 - 10 MB), the SQL Server buffer pool will continue to grow. As other processes on the same computer as SQL Server allocate memory, the SQL Server buffer manager will release memory as needed. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.

    • 8

  2. Because of the way SQL Server allocates memory (particularly with AWE enabled), Task Manager and Process Explorer will not give you an accurate indication of how much RAM it's actually using. To get the proper figures, you have to ask SQL Server directly. The easy way to do that is run perfmon, and add the counter SQLServer:Memory Manager/Total Server Memory. That counter will report the total memory usage in KB.

    Now, if SQL Server is using the full 6000 MB that it's limited to, then that's perfectly normal (and desirable) behavior. If it's using substantially more than that, then there might be a problem.

    • 1