asked
sql server 2008 standard edition 64 bit
hi team, we user 16 GB RAM in our sql server, and these days we have started facing a issue **There is insufficient system memory in resource pool 'internal' to run this query. Error: 701, Severity: 17, State: 65.** and we have decided to place a memory cap, but question is how to decide the maximum memory allocated to the sqlservr.exe thanks
Ramesh - Glen Berry has a nice list of limits that can be considered a good guideline: [Glen Berry - Suggested Max Memory settings for SQL 2005/2008][1] So you should be aiming for about 2-3GB reserved for Windows and the rest for SQL Server - obviously, this may need changing if the machine is doing other things than just SQL Server. [1]:
http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx
Just as a comparison we have servers here that are running SQL Std 64bit with 2GB reserved for the OS and other processes and dont have any issues. I would be very wary of dropping below 2GB though, depending on OS and other applications/services that the server is also running.
Dont worry WilliamD, @Pavel will delete what you said in a minute or two and it will be lost forever anyway!!!
Absolutely, pick a figure, see if you get errors, inc/dec a bit until you are happy.
@Ramesh - We are using same 64bit Server 2008 with 16GB of Memory. What I done is set 2 GB for OS and all the rest to server. You can run the following script to change the SQL Max Memory.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 14336;
GO
RECONFIGURE;
GO
You can also look at the following link from Microsoft -
[Setting Server Memory][1]
[1]: http://msdn.microsoft.com/en-us/library/ms178067.aspx
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 14336;
GO
RECONFIGURE;
GO
You can also look at the following link from Microsoft -
[Setting Server Memory][1]
[1]: http://msdn.microsoft.com/en-us/library/ms178067.aspx
hi team. as we use 16 GB RAM for our sql server 2008 standard editon 64 bit, i have placed a memory cap at 13.5 GB, but still m,emory goes on increasing,if any help appreciated
Ramesh, do you mean that SQL Server still takes up more than the limit you set?
not surprised. So long as it doesnt go much above that then I would say its normal from the experience of the servers that we have here. It never matches to the byte. If you really need the 600MB then set your limit to 13GB and see how that goes. I have never seen it much over the set limit but it is almost predictably over the limit whenever I look.
