memory settings

Question

Post reply

memory settings

sqlfriend

SSC Guru

Points: 52781
More actions
November 22, 2010 at 1:49 pm

#157331

I'm going to setup a new sql server 2005 on a virtual server 64 bits, 8 gb memory. Then move our databases from old server to this one.
My question is how should I setup memories on Sql server Properties - Memory screen. What is the most common set up for this screen.
I see there is a Maximum server memory (in MB) 2147483647, this is really big, what should I change this to?
THanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 1

I see there is a Maximum server memory (in MB) 2147483647, this is really big, what should I change this to?

This is the default memory option

you can just leave it like that because sql takes care of server memory usage dynamically.

if you want set forcebily you can set it to

(total memory your system has - 2GB (for os) = Max memory)

Regards,

Pavan

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Our network guy says the memory is not correct if I use default one.

So the default is (in MB) 2147483647, is this PB already? How come it is so huge size?

Our physical memory is 8 gb, so how can I change maximum for sql server to 6 gib?

Is there a command line, or shall I directly change in the box with 2147483647, what number should I change it to , I'm really confused.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 3

There should be no problem even if it is the default

If you want to change you can change

Directly in the field where is the default

and also you can change through exec sp_configure 'max server memory (MB)','6*1024'

Server restart is imp to take effect

See here

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

JeremyE SSCoach Points: 16201 More actions · Answer 4

You should always set max memory for SQL Server to something less than the max memory on the machine. If not, SQL could potentially consume all the memory on the box and starve out the operating system.

No restart is needed to set min and max memory.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 5

I did before changing directly in the field it shows you there it is changed but when you see in sp_configure it won't show you check under run_value is the old one untill you run reconfigure command.

Check here

SQL Server and Os memory dynamically exchanges memory as per requirement even if you set it to default or 2gb less than physical memory

The only concern with min memory option read the above mention blog.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

Thank you all.

Above question is all about my new server set up.

By the way for my current existing physical server, it also has 8 gb ram, but I can see it uses up to 98%, can I change it to by using

following statement, will that affect the application databases running on the server?

Thanks

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'max server memory', 6144;

GO

RECONFIGURE;

GO

grahamc SSCertifiable Points: 6762 More actions · Answer 7

do your research on 64bit systems carefully! 64bit systems require more memory for the OS. I believe its along the lines of 5GBs to the OS.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

My vote is for 5.5GB max memory for this machine, assuming the only thing running on it is the SQL Server relational engine. Monitor for paging to ensure this is a good number.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

sqlfriend SSC Guru Points: 52781 More actions · Answer 9

sqlfriend

SSC Guru

Points: 52781

November 24, 2010 at 7:22 am

#1255082

Thank you! that's helpful.