x

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

more ▼

asked Dec 07, 2010 at 10:30 PM in Default

ramesh 1 gravatar image

ramesh 1
2.2k 63 67 69

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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
more ▼

answered Dec 08, 2010 at 12:00 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Dec 08, 2010 at 01:15 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

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)
Dec 08, 2010 at 01:25 AM WilliamD
Absolutely, pick a figure, see if you get errors, inc/dec a bit until you are happy.
Dec 08, 2010 at 01:29 AM Fatherjack ♦♦
So what you're trying to say is... "It depends!"
Dec 08, 2010 at 01:53 AM ThomasRushton ♦
Well, we're not supposed to do that now, are we?! ;-\
Dec 08, 2010 at 02:10 AM WilliamD
Dont worry WilliamD, @Pavel will delete what you said in a minute or two and it will be lost forever anyway!!!
Dec 08, 2010 at 07:46 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

@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
more ▼

answered Dec 08, 2010 at 04:52 AM

Leo gravatar image

Leo
1.6k 54 56 58

(comments are locked)
10|1200 characters needed characters left
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
more ▼

answered Dec 12, 2010 at 10:15 PM

ramesh 1 gravatar image

ramesh 1
2.2k 63 67 69

Ramesh, do you mean that SQL Server still takes up more than the limit you set?
Dec 13, 2010 at 12:10 AM David Wimbush
yes still it grows up to 14.1 GB in the working hours
Dec 13, 2010 at 03:33 AM ramesh 1
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.
Dec 13, 2010 at 03:55 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1842
x58
x24

asked: Dec 07, 2010 at 10:30 PM

Seen: 2456 times

Last Updated: Dec 08, 2010 at 12:01 AM