Server Max Memory Setting

Question

Post reply

Server Max Memory Setting

dmc-608719

SSCarpal Tunnel

Points: 4663
More actions
October 21, 2008 at 7:22 am

#125603

I am having issues in the log that indicate a large amount of memory paged out on my SQL 2K5 Ent x64 with 32GB ram. So I want to set the lock pages in memory setting and set the max memory setting. Trying to figure out what value to set. Here are my two initial questions:
1. Do I need to restart the SQL Service to get the new max memory setting to take affect or will it be implemented immediately?
2. I see a lot of articles about determining how much ram to leave for the OS. How is this done? What counters should I look at? Do I set a value and then look, or determine the value to use before setting? This machine is solely a SQL server and runs nothing but SQL, Analysis Services, SSIS, and DBA style Reporting Services

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jason Crider SSCertifiable Points: 6019 More actions · Answer 1

I am running with 16GB and I did the following:

sp_configure 'awe enabled', 1

go

reconfigure with override

go

sp_configure 'max server memory (MB)', 14336

go

reconfigure with override

go

Stop and start SQL Server. I chose the max memory of 14GB, since you should

leave 2GB for the OS.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 2

Forget about AWE for x64 ! Thats what it's all about with 64-bit.

You don't need that unless you go over the standard 64-bit addressing window.

However, you need to set your max server memory ! or it will slowly eat up all your server memory !

As for "lock pages in memory" only use it if you need to.

Gail has got a couple of good refs pro and contra.

(I cannot find them right now)

http://sqlinthewild.co.za/index.php/category/sql-server/

This points to the ref I intended http://www.sqlservercentral.com/Forums/FindPost563070.aspx

How much of the available ram do you expect your sqlserver instance to consume. Make it a reasonable size.

Just make sure you leave enough memory for all other stuff that runs on the server (OS, SQLAgent, anti virus, backup software, monitoring stuff, ....)

some other refs:

http://www.sqlservercentral.com/articles/News/abitabout64bit/1360/

http://www.sqlservercentral.com/Forums/Topic409075-360-1.aspx

http://www.sqlservercentral.com/Forums/Topic379718-360-1.aspx

http://www.sqlservercentral.com/Forums/Topic564826-146-1.aspx

happy reading 😉

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Mark Sumner SSCommitted Points: 1851 More actions · Answer 3

1. Assuming the memory limit has not been reached the setting will come into effect immediately. There is no requirement to restart the SQL server service to set a memory limit

2. You should probably leave at least 2Gb for the OS.

You can use perfmon or process explorer to monitor memory usage. In perfmon - add a counter for Process | Private bytes. Private Bytes will tell you how much size, in bytes, of physical memory a process has allocated that cannot be shared with other processes. Or in Process explorer - View | Select Columns | Process Memory | Private bytes.

Jerry Hung SSChampion Points: 12968 More actions · Answer 4

Mark Sumner (10/22/2008)
1. Assuming the memory limit has not been reached the setting will come into effect immediately. There is no requirement to restart the SQL server service to set a memory limit

Another new thing learned, thanks for the tip

I have always restarted the SQL Server just to be safe

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 5

Thanks for answering the questions. I changed the value and it changed the running value as well. So seems no restart is needed.

I will take a look at those counters. I am also going to look at pages/sec to see if any excessive paging is occuring to determine if I need to adjust the amount of memory.

ramoha SSCommitted Points: 1909 More actions · Answer 6

Hi,

FYI, The statement Reconfig with over_ride for Max Memory setting doesn't require a restart.

Razi, M.
http://questivity.com/it-training.html

sunderwood Grasshopper Points: 12 More actions · Answer 7

We recently had the same problem, running SQL server 2005 x64 on Windows 2003 64bit, and would have problems with memory being paged out. Out solution was to install sp2 for Windows 2003 and our problem was solved.