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.

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

share|improve this question
    
Meant to send this to ServerFault, clicked on Meta because it's too early in the morning. –  Eric Sep 14 '09 at 11:27
    
Cool, didn't know that existed.. Thanks –  Suiva Sep 14 '09 at 11:31
add comment

4 Answers

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

share|improve this answer
    
Thanks :) Must have missed that part –  Suiva Sep 14 '09 at 11:48
add comment

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%5Fkehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx

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

share|improve this answer
add comment

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."

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.