SQL 2008 Memory Usage

memory usagesql-server-2008

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.

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.

Related Topic