2

I am having trouble with restricting SQL Server 2008's memory usage on our Windows Server 2008 server. The system has 11GB of RAM, I have tried restricting SQL's memory usage to 8.5GB by setting "Maximum server memory (in MB)" to 8500 but it is still currently using over 9.5GB of memory (even after a reboot).

Any ideas how to restrict it properly?

Thanks for your time

2
  • Meant to send this to ServerFault, clicked on Meta because it's too early in the morning.
    – Eric
    Sep 14, 2009 at 11:27
  • Cool, didn't know that existed.. Thanks
    – Suiva
    Sep 14, 2009 at 11:31

4 Answers 4

5

These settings apply to the Buffer Pool only. SQL Server also allocates memory from outside of this region. See the following excerpt from SQL Server Books Online

SQL Server as a process acquires more memory than specified by max server memory option. Both internal and external components can allocate memory outside of the buffer pool, which consumes additional memory, but the memory allocated to the buffer pool usually represents the largest portion of memory consumed by SQL Server.

For further readining see: SQL Server Memory Options

0
3

The Max Memory setting does not cover everything that SQL Server does, but only the buffer pool, which I'd loosely describe as data held in memory rather than on disk.

From BOL :

"SQL Server as a process acquires more memory than specified by max server memory option. Both internal and external components can allocate memory outside of the buffer pool, which consumes additional memory, but the memory allocated to the buffer pool usually represents the largest portion of memory consumed by SQL Server."

1

I think John is right on money on this money. With Max server memory, one can control only the Data cache/buffer pool area only. SQL Server will still use memory for other areas like Extended Procedures, OLE Automation calls, Linked Servers, OLEDB providers, SQL CLR, and also used for allocations larger than 8KB (>8KB), also known as multi-page allocations.

Jonathan Kehayias has couple of excellent posts to understand these areas and you may want to catch up on these when you get a chance.

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

0

How are you measuring the memory use? Performance monitor counters? A display in the SQL tools? Task Manager?

It may be that the reading you are looking at is not showing exclusively the memory pool that you are trying to limit. If you tell us where you are taking readings from, you will hopefully get a more specific answer.

You must log in to answer this question.