October 18, 2007 at 12:54 pm
In the SQL 2005 server memory properties, the Maximum server memory option says "(in MB)" and the value is 2147483647 for my test server. Is that really MB or is it 2 GB expressed in bytes?
That server has 4 GB of RAM but SQL, according the performance monitor, is using only about 1.6 GB. I want to add the /3GB switch for Windows and set the max memory to 3 GB but I want to know whether I set the max memory option to 3221225472 (3 GB in bytes) or 3072 (3 GB in MB).
Also, for a SQL server with 8 GB of RAM, would I need to follow this advice, which I found in the Wrox book Professional SQL Server 2005 Administration?
32-bit OS and SQL with 4 to 16GB of RAM: In this case, you need PAE to enable the system to access more than 4GB of RAM. You will also want to use AWE to enable SQL to access as much memory as possible. You might want to consider turning on /3GB as well.
I want to set up the 8 GB server so that it allows SQL to use up to 6 GB of RAM. Right now the server appears to be using about 1.6 GB. It is not in production, though, so I do not want it to be pinned down to 2 GB if it needs to go to 5 or 6.
Thanks for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 18, 2007 at 1:03 pm
/3GB switch as nothing to do with RAM. this /3GB switch is for VAS(Virtual Address Space) so by having this switch in boot.ini your kernel mode will get 1GB of VAS & Usermode will get 3GB of VAS. I will recommend to keep memory Dynamic rather than min-max for 4GB RAM.
For 8GB, depends how your database is design, size, indexes etc..
min-max is for buffer pool, so i don't want that you confuse with something else. you can set min-max once you know the pros-cons of this setting.
I hope this helps.
October 18, 2007 at 2:26 pm
sqldba (10/18/2007)
/3GB switch as nothing to do with RAM. this /3GB switch is for VAS(Virtual Address Space) so by having this switch in boot.ini your kernel mode will get 1GB of VAS & Usermode will get 3GB of VAS. I will recommend to keep memory Dynamic rather than min-max for 4GB RAM.For 8GB, depends how your database is design, size, indexes etc..
min-max is for buffer pool, so i don't want that you confuse with something else. you can set min-max once you know the pros-cons of this setting.
I hope this helps.
Thanks for your reply.
Our database is about 17 GB and appears to have been heavily stressed on our SQL Server 2000 Standard Edition server. Even though that server has 8 GB of RAM, SQL never uses more than about 1.8 GB. I recently found out that, by design, SQL Standard Edition doesn't even use more than 2 GB, so the rest of the RAM is not being used by SQL. I am trying to figure out whether the bottleneck is RAM.
In my monitoring I have seen Memory: Pages/sec extremely high (averaging from 100-400 with spikes above 1000) and lots of long-running queries and blocking. I don't know if the problem is with the memory or hardware or with the queries. Part of the issue appears to be due to several feeds that run at various hours.
We are planning to move the database to SQL Server 2005 Standard Edition with 8 GB of RAM, and it looks like that edition runs with 2 GB of RAM by default, although I have read that it is not restricted to 2 GB like SQL 2000 Standard. But I want to make sure that when we move the database to SQL 2005, it doesn't end up not using the additional memory.
I know that there is probably a combination of causes (a couple of tables with > 1 million records, 17 GB database size, possible unoptimized queries, large batch and backup jobs running, etc.), but I think that having more memory available can at least be a step in the right direction and buy us time to work on some of the tougher issues. That is why I have been researching the /3GB switch and AWE.
If you have any advice given the scenario above, I would be very grateful!
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 18, 2007 at 3:36 pm
From my research, this is what I recommend:
Set AWE to enabled.
Run SQL Server under a Windows account that has Lock Pages in Memory enabled.
The Maximize data throughput for network application option of Network Connection in Control Panel must be selected.
Add the /PAE switch to the boot.ini OR the /3GB switch (not both). Test both to see which works better. The /PAE switch is generally considered better.
Then get on optimizing those queries ASAP.
October 18, 2007 at 4:24 pm
switch /PAE only comes in to the role if your server as RAM above 16GB. If your Server as more than 16GB of RAM then you need to take out /3GB switch if it is in place in boot.ini
October 22, 2007 at 10:01 pm
i hope this will help & make sense up to the extent.
4GB RAM: /3GB (AWE support is not used)
8GB RAM: /3GB /PAE
16GB RAM: /3GB /PAE
16GB + RAM: /PAE
For 64bit SQL Server, use min-max memory after carefull consideration.
no need of /3GB or PAE in 64 bit hardware & software.
October 24, 2007 at 8:19 am
Hello
Is it also true that you must be running SQL Server 2005 EE in order to take advantage of the space available by using PAE? Standard Edition cannot use it?
October 24, 2007 at 8:29 am
October 25, 2007 at 9:03 am
Hello
In reference to VAIYDEYANATHAN's post
I'll interpret your reply as True, only EE will take advantage of the memory exposed through PAE
Thanks
October 25, 2007 at 8:41 pm
SQL 2005 SE can use as much memory as OS supports.
for more than 4Gb I'll use /3gb and enable AWE, Lock pages in memory
on your 2000 SE instance don't do any of these options. SE can't use more than 2gb no matter what u do.
October 26, 2007 at 10:10 am
Hello
Thanks for the reply.
I can realize a large cost savings if I can use SE. There are no features in EE right now that my company needs. We also have alot of SQL Servers installed with 4GB or less of ram, yet all have multiple, high end processors. I want to try to consolidate some of these during the upgrade process. The savings comes in if I can migrate some to 2K5 SE onto boxes with 16GB ram.
November 5, 2007 at 11:53 am
russell (10/25/2007)
SQL 2005 SE can use as much memory as OS supports.for more than 4Gb I'll use /3gb and enable AWE, Lock pages in memory
on your 2000 SE instance don't do any of these options. SE can't use more than 2gb no matter what u do.
Thanks - sorry to ask yet more questions, but can SQL 2005 SE use more than 4 GB of RAM even if it is only the 32-bit version?
The installation I am looking is running Windows 2003 Server Enterprise Edition and SQL 2005 SE, both 32-bit as far as I can tell - select @@version returns 9.00.3042, Intel X86, and Build 3790 SP2 - does that mean the OS and SQL are both 32-bit?
Also, as a side question, is 9.00.3042 the wrong SP2? What SQL version represents the correct SP2 and the complete patching up to the current hot fixes? 9.00.3200 as listed here[/url] in the build list?
Thanks again!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 6, 2007 at 12:46 pm
Excuse me, but it seems that not all of your questions have been answered yet.
See this kb article for info regarding correct memory setting and general info (follow the other links on this page also):
http://support.microsoft.com/kb/274750/
This one about large memory in windows
http://support.microsoft.com/kb/283037/
Another great post, is this one regarding memory management in general:
http://blogs.technet.com/askperf/archive/2007/03/23/memory-management-demystifying-3gb.aspx
In my personal experience you would almost always do well to set the min and max values equal to the amount you want to allocate to sql server. In any case as long as you have no other memory intensive application on the same server. Leave between 1 and 2 GB for kernel mode depending on total amount of memory (4 GB RAM, 1 GB for kernel or 16 GB RAM, 2 GB for kernel)
In the SQL 2005 server memory properties, the Maximum server memory option says "(in MB)" and the value is 2147483647 for my test server. Is that really MB or is it 2 GB expressed in bytes?
The value is equal to unlimited, but yes it really is MB. E.g. you want allocate 3 GB of memory then use 3072 (= 3 GB). You won't get all 3 GB, because certain system memory features will prevent that, but you'll get around 2,7 GB ( see the above links).
In your other case (the 8GB system) you,ll have to use PAE and/or AWE. I assume you use Windows 2003 (32-bit).
/Mekal
November 7, 2007 at 7:12 am
mekal inc (11/6/2007)
Excuse me, but it seems that not all of your questions have been answered yet.See this kb article for info regarding correct memory setting and general info (follow the other links on this page also):
http://support.microsoft.com/kb/274750/
This one about large memory in windows
http://support.microsoft.com/kb/283037/
Another great post, is this one regarding memory management in general:
http://blogs.technet.com/askperf/archive/2007/03/23/memory-management-demystifying-3gb.aspx
In my personal experience you would almost always do well to set the min and max values equal to the amount you want to allocate to sql server. In any case as long as you have no other memory intensive application on the same server. Leave between 1 and 2 GB for kernel mode depending on total amount of memory (4 GB RAM, 1 GB for kernel or 16 GB RAM, 2 GB for kernel)
In the SQL 2005 server memory properties, the Maximum server memory option says "(in MB)" and the value is 2147483647 for my test server. Is that really MB or is it 2 GB expressed in bytes?
The value is equal to unlimited, but yes it really is MB. E.g. you want allocate 3 GB of memory then use 3072 (= 3 GB). You won't get all 3 GB, because certain system memory features will prevent that, but you'll get around 2,7 GB ( see the above links).
In your other case (the 8GB system) you,ll have to use PAE and/or AWE. I assume you use Windows 2003 (32-bit).
/Mekal
Thank you so much for your post,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 8, 2007 at 10:45 am
mekal inc (11/6/2007)
Excuse me, but it seems that not all of your questions have been answered yet.See this kb article for info regarding correct memory setting and general info (follow the other links on this page also):
http://support.microsoft.com/kb/274750/
This one about large memory in windows
http://support.microsoft.com/kb/283037/
Another great post, is this one regarding memory management in general:
http://blogs.technet.com/askperf/archive/2007/03/23/memory-management-demystifying-3gb.aspx
In my personal experience you would almost always do well to set the min and max values equal to the amount you want to allocate to sql server. In any case as long as you have no other memory intensive application on the same server. Leave between 1 and 2 GB for kernel mode depending on total amount of memory (4 GB RAM, 1 GB for kernel or 16 GB RAM, 2 GB for kernel)
In the SQL 2005 server memory properties, the Maximum server memory option says "(in MB)" and the value is 2147483647 for my test server. Is that really MB or is it 2 GB expressed in bytes?
The value is equal to unlimited, but yes it really is MB. E.g. you want allocate 3 GB of memory then use 3072 (= 3 GB). You won't get all 3 GB, because certain system memory features will prevent that, but you'll get around 2,7 GB ( see the above links).
In your other case (the 8GB system) you,ll have to use PAE and/or AWE. I assume you use Windows 2003 (32-bit).
/Mekal
Hello again, I have one further set of questions. I hope you can shed some light on them.
We have an active/passive cluster. The active node has 8 GB of RAM and the passive node has 4 GB. I did not have a say in the RAM chosen, but I have since read that best practice is to have both nodes identical. So that is issue 1, but I will have to see if I can get approval for the extra RAM.
In terms of the current setup then, these are my questions.
1. Do we need 64-bit SQL Server Standard Edition AND 64-bit Windows 2003 Server Enterprise Edition to use more than 2 GB of RAM with SQL 2005? More than 4 GB?
2. Can the amount of RAM used by SQL 2005 be set independently for each node, or are we limited to the max available in the weaker node in case of failover?
3. How much RAM can SQL use with the current setup - 32-bit OS, 32-bit SQL 2005, and 8/4 GB of RAM? I assume it is no more than 3 GB (given the weaker node) but I'm not sure if this depends on being able to set max memory for each node separately.
Thanks again for any help.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply