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

asked Dec 07 '10 at 10:30 PM in Default

ramesh 1
1.8k ● 48 ● 64 ● 68

(comments are locked)

4 answers:

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

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.

answered Dec 08 '10 at 12:00 AM

WilliamD
25.4k ● 16 ● 18 ● 41

(comments are locked)

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.

answered Dec 08 '10 at 01:15 AM

Fatherjack ♦♦
38.8k ● 56 ● 73 ● 104

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 '10 at 01:25 AM WilliamD

Absolutely, pick a figure, see if you get errors, inc/dec a bit until you are happy.

Dec 08 '10 at 01:29 AM Fatherjack ♦♦

So what you're trying to say is... "It depends!"

Dec 08 '10 at 01:53 AM ThomasRushton ♦

Well, we're not supposed to do that now, are we?! ;-\

Dec 08 '10 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 '10 at 07:46 AM Fatherjack ♦♦

(comments are locked)

@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

answered Dec 08 '10 at 04:52 AM

Leo
1.6k ● 49 ● 54 ● 58

(comments are locked)

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

answered Dec 12 '10 at 10:15 PM

ramesh 1
1.8k ● 48 ● 64 ● 68

Ramesh, do you mean that SQL Server still takes up more than the limit you set?

Dec 13 '10 at 12:10 AM David Wimbush

yes still it grows up to 14.1 GB in the working hours

Dec 13 '10 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 '10 at 03:55 AM Fatherjack ♦♦

(comments are locked)