Memory question

Question

Memory question

Frank Kalis

SSC Guru

Points: 111183
More actions
November 17, 2003 at 6:32 am

#59912

hi there,
bear with me for this is a rather easy one, but I'm not that much into hardware .
Our network admin has configured SQL Server to allocate dynamically memory. Now this is the Standard Edition which can only adress 2GB RAM. The Server has 3 GB of RAM.
Is he able to configure SQL Server to use 2.8 GB, although this is beyond the maximum and SQL will not use it anyway?
Frank
http://www.insidesql.de
http://www.familienzirkus.de
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

You can configure SQL Server Standard to use more than 2GB memory, but then it will not use physical memory above 2GB but instead virtual (paged) memory, and you do not want this.

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

So I am able to see in EM 3071 MB of RAM ?

Although I can only use 2048??

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
So I am able to see in EM 3071 MB of RAM ?
Although I can only use 2048??
Frank

I'm not sure about EM, IIRC it limits you to physical memory but is not bounded by the Standard Edition limitation. I know you can set max memory to virtually anything with sp_configure.

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hold it, Jonathan....

KISS, I'm not going to talk to the network admin about using a stored procedure. No, no EM is just fine for him

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

JFW Mr or Mrs. 500 Points: 546 More actions · Answer 5

We are running SQL Server 2000, SP3 Standard Edition on Windows Server 2000 SP4 with 4 gig of memory and a 4 gig swap file. My current settings in EM on the Memory Tab are Dynamically configured SQL Server Memory with a minimum of 0MB and a maximum of 3712MB. Sql Server is the only application (single instance) running on this box. Task Manager shows only 1700MB used by the SQL Server process. Based upon this thread should I

1-Lower the maximum memory to 2048MB?

2-Use the fixed memory option set to 2048MB?

3-Leave it alone?

Thank you for your recommendation.

Jonathan SSC-Insane Points: 20427 More actions · Answer 6

quote:
We are running SQL Server 2000, SP3 Standard Edition on Windows Server 2000 SP4 with 4 gig of memory and a 4 gig swap file. My current settings in EM on the Memory Tab are Dynamically configured SQL Server Memory with a minimum of 0MB and a maximum of 3712MB. Sql Server is the only application (single instance) running on this box. Task Manager shows only 1700MB used by the SQL Server process. Based upon this thread should I
1-Lower the maximum memory to 2048MB?
2-Use the fixed memory option set to 2048MB?
3-Leave it alone?
Thank you for your recommendation.

Leave it alone. You can also take some of that excess memory out of this server and insert it into another server that could make use of it.

--Jonathan

Eddie Wuerch SSChampion Points: 12532 More actions · Answer 7

Also note that you're going to cough up half your physical RAM to the OS. If your SQL box has 4GB of RAM on Win2000 Server, then applications can only get to 2GB of it. SQL Server dynamic memory allocation tries to grab the most RAM (up to any preset max) it needs without causing swapping. To avoid causing the server to start swapping, it will leave some physical memory left for other applications, even if your preset max is much higher.

So pulling 1 GB out of the server for use elsewhere will reduce the amount of memory available to SQL Server, even if it looks like the server never touches it.

Even if you're not running applications on your SQL box, there are other services and stuff that will need memory from the application side of the 50% RAM split, and SQL will leave some for them, too.

I've seen a couple instances of SQL Standard/Win2k w/4GB of RAM, and the most SQL ever allocated, even under heavy use, was about the 1.7GB mark.

If your Server is purely a SQL Server, then it really doesn't need 2GB for the OS to run without swapping. Familiarize yourself with the Performance Monitor and some of the memory and disk activities that can be monitored, such as Page Faults (meaning the server reached for something in physical memory, but it was in the page file instead). You'll need to know if this next trick is helping or hurting.

Win2000 can be configured to change the physical memory split from 50% OS / 50% apps to 25% OS / 75% apps if the server has 4GB of RAM, but you'll have to edit you boot.ini file to do it.

(** Disclaimer ** If you've never modified the boot.ini file, then either hand this info to someone who has, or stop reading. Cover yourself by backing this file up to floppy, updating your ERD, installing the recovery console to disk, etc. in case this goes horribly wrong. Which it can. Quickly.)

In the boot.ini file, on the line that specifies your boot partition/OS info (under the [operating systems] heading), add the /3GB switch.

So this line which normally looks something like this:

[operating systems]

multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server" /fastdetect

would look like this (for illustration purposes only - do NOT copy this text to your boot.ini file):

[operating systems]

multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server" /fastdetect /3GB

If you go this route, be sure to set some time aside to monitor for any adverse effects (mainly additional swap file activity). You should also see SQL Server using more physical RAM.

-Eddie

eddie@mirai-tech.com

Eddie Wuerch
MCM: SQL

Jonathan SSC-Insane Points: 20427 More actions · Answer 8

quote:
Also note that you're going to cough up half your physical RAM to the OS. If your SQL box has 4GB of RAM on Win2000 Server, then applications can only get to 2GB of it. SQL Server dynamic memory allocation tries to grab the most RAM (up to any preset max) it needs without causing swapping. To avoid causing the server to start swapping, it will leave some physical memory left for other applications, even if your preset max is much higher.

It attempts to leave 5MB free; which is not much to worry about when we've got 2GB of user-mode memory space...

quote:
So pulling 1 GB out of the server for use elsewhere will reduce the amount of memory available to SQL Server, even if it looks like the server never touches it.

I'm not sure where this idea comes from. As the kernel certainly doesn't need 2GB, reducing its space from 2GB to 1GB (the same thing that using /3GB on Enterprise Edition does), will have no effect on user-mode programs like SQL Server.

quote:
Even if you're not running applications on your SQL box, there are other services and stuff that will need memory from the application side of the 50% RAM split, and SQL will leave some for them, too.
I've seen a couple instances of SQL Standard/Win2k w/4GB of RAM, and the most SQL ever allocated, even under heavy use, was about the 1.7GB mark.
If your Server is purely a SQL Server, then it really doesn't need 2GB for the OS to run without swapping. Familiarize yourself with the Performance Monitor and some of the memory and disk activities that can be monitored, such as Page Faults (meaning the server reached for something in physical memory, but it was in the page file instead). You'll need to know if this next trick is helping or hurting.
Win2000 can be configured to change the physical memory split from 50% OS / 50% apps to 25% OS / 75% apps if the server has 4GB of RAM, but you'll have to edit you boot.ini file to do it.
(** Disclaimer ** If you've never modified the boot.ini file, then either hand this info to someone who has, or stop reading. Cover yourself by backing this file up to floppy, updating your ERD, installing the recovery console to disk, etc. in case this goes horribly wrong. Which it can. Quickly.)
In the boot.ini file, on the line that specifies your boot partition/OS info (under the [operating systems] heading), add the /3GB switch.
So this line which normally looks something like this:
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server" /fastdetect
would look like this (for illustration purposes only - do NOT copy this text to your boot.ini file):
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server" /fastdetect /3GB
If you go this route, be sure to set some time aside to monitor for any adverse effects (mainly additional swap file activity). You should also see SQL Server using more physical RAM.
-Eddie
eddie@mirai-tech.com

As the questioner explicitly wrote that he's using SQL Server Standard Edition on Windows 2000 Server, setting the /3GB switch will not gain anything and is explicitly not supported in a production environment.

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

quote:
Leave it alone. You can also take some of that excess memory out of this server and insert it into another server that could make use of it.

<*grin*> Yes, I opted here for our 1 GB RAM.

They weren't amused at all.

Interestingly the managers shot themselves in the foot. They just look at licensing cost for both editions, stated they requirements and decidedä that Standard Edition is sufficient....

Against stupidity.......

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]