question

ramesh 1 avatar image
ramesh 1 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
sql-server-2008memory64-bit
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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.
11 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So what you're trying to say is... "It depends!"
2 Likes 2 ·
Dont worry WilliamD, @Pavel will delete what you said in a minute or two and it will be lost forever anyway!!!
1 Like 1 ·
Definitely a suck it and see - I don't like giving hard numbers for questions like this as you never know the whole truth. 2GB would be my minimum in this situation too. Have changed my answer to reflect that (2-3GB)
0 Likes 0 ·
Absolutely, pick a figure, see if you get errors, inc/dec a bit until you are happy.
0 Likes 0 ·
Well, we're not supposed to do that now, are we?! ;-\
0 Likes 0 ·
Show more comments
Leo avatar image
Leo answered
@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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ramesh 1 avatar image
ramesh 1 answered
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
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ramesh, do you mean that SQL Server still takes up more than the limit you set?
0 Likes 0 ·
yes still it grows up to 14.1 GB in the working hours
0 Likes 0 ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.