April 28, 2008 at 12:55 pm
I'm just trying to get a handle on this one.
I'm looking at running SQL 2005 Standard 32 bit on a Win 2003 Enterprise 32 bit system. We are locked into 32 bit because of delivered apps. I have some apps that are killing the system because they are highly transactional and we're running into disk queue length and other issues. We want to crank the server up to 12GB of RAM and run instances.
I know that if you do not have AWE enabled and you set the max server memory to 3072 (3GB) it will bring SQL to its knees. That is 3072MB not GB. But if you have AWE enabled that supposedly will be 3GB. I'm looking at having to run 3 separate instance because vendors have different requirements and such and to beat down the issues of memory. So if I have 1 instance that I have at 756 of memory (non-AWE) and then want to run the other 2 instances with AWE enabled at 3GB each this would be controlled with the max server memory setting and I can limit it that way.
Is it documented anywhere that the "max server memory (MB)" setting acts differently when AWE is enabled? That 3072 is 3GB or 5120 is 5GB?
name minimum maximum config_value run_value
max server memory (MB) 16 2147483647 2147483647 2147483647
min server memory (MB) 0 2147483647 268435455 268435455
If you look at Enabling AWE Memory for SQL Server they show doing this, but don't show that the AWE actually changes how the max server memory option operates.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
April 28, 2008 at 1:04 pm
awe is an o/s setting, via /pae switch, that enables application to address extended memory. For sql server to access this memory then you have to set awe on and set max memory, min memory does nothing with awe enabled.
Out of the box sql will use 2gb private address space per instance assuming your server has enough memory. If you set the /PAE switch then you may allocate extra memory to any instance as you see fit. With 12Gb ram I'd set the /PAE switch ( no /3GB ) enable awe on for each instance and set max memory to maybe 3Gb or 3072MB - you'd need to be careful as 3Gb may not be enough for the o/s with 3 instances.
Not sure I follow your route about 3072 and 3Gb - these are the same. Note that with awe enabled there is no dynamic memory and min memory setting does nothing.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 28, 2008 at 1:33 pm
SQL 2000 forced fixed memory usage with AWE enabled, but SQL 2005 does not.
Here is a pretty good article explaining in detail:
There are a couple of important things to note.
AWE memory usage is not just slower than direct memory access, but it is restricted in it's use. There is a limited subset of memory operations SQL Server will use AWE memory for, so you still need direct-addressed physical memory for each of your instances.
SQL 2005 dynamic memory management is MUCH better than it was with SQL 2000 - do some testing, but I have found that in most of my installs, it managed memory best dynamically even if there was nothing else on the server.
You are going to have to do some testing, but if you are getting memory pressure from other applications running on your 32 bit SQL server and you already have 4gb of memory in the server, it is optimistic to think that enabling AWE and increasing the memory is going to solve the problem.
April 28, 2008 at 1:43 pm
Run
exec SP_configure 'show advanced options' ,1;
reconfigure with override;
and you get the following back
name minimum maximum config_value run_value
Ad Hoc Distributed Queries 0 1 1 1
...............
allow updates 0 1 0 0
awe enabled 0 1 0 0
blocked process threshold 0 86400 0 0
.......................
max full-text crawl range 0 256 4 4
max server memory (MB) 16 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
............................
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
...............................
>> Not sure I follow your route about 3072 and 3Gb - these are the same.
If you don't have AWE enabled and you run
exec SP_configure 'max server memory (MB)', 3072;
reconfigure with override;
You will crush the amount of memory that SQL server has to 3072 MB. Trust me, I've seen it. I want to be able to fire up SQL with AWE enabled and still restrict the amount of memory it takes, so that it doesn't go to 10 GB on a 12GB box, but I am over the 2GB limit.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
April 28, 2008 at 1:55 pm
.........but if you are getting memory pressure from other applications running on your 32 bit SQL server and you already have 4gb of memory ..........
That is why I'm looking at upping the memory and doing multiple instances. I have a baby instance that only has 1 user DB in it, and runs fine on about 768. Then I have a huge instance with many DBs that is experiencing the problem. So if I break it into a 3GB instance with some of the databases and a different 3GB instance with some of the others, I was hoping to balance it out. But if you can't control the memory in AWE, then you have to go back to the drawing board.
With my PHB it is easier to convince him to up RAM and then we can get away with less licensing cost.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
April 28, 2008 at 2:03 pm
You say you are locked into 32 bit because of some delivered apps, but you may want to look into the feasibility of 64 bit.
If you can move the other apps to another server, or even run them in WOW, you may be ok.
It may be cheaper than any other option at this point.
April 28, 2008 at 2:16 pm
We tried.
Several of our apps are heavily into the COM/DCOM world. We're stuck. Hopefully the vendors will change that with the advent of 2008, but we have to wait.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
April 29, 2008 at 1:52 pm
It seems to me there's still so much lack of understanding how memory works with SQL Server. Forget x64 and consider x32. You only have three options ( + one suicidal option ) and it doesn't matter if it's sql 2000 or sql 2005 as memory is an o/s thing basically.
1. Use no switches in boot.ini - sql server can use 2Gb of memory
2. use the /3GB switch - sql server can use 3GB of memory
3. Use the /PAE switch - sql server can use awe memory up to whatever you decide. ( as long as you enable awe in sql server )
Suicidal . /3GB + /PAE switch
--------------------------------
For 1. and 2 you do not need to configure memory unless you have some reason to limit memory for sql server - you'll get around 1.7gb and 2.7gb available memory to play with, sql server will attempt to balance the memory - thus with the 3Gb switch this can increase the size of the various pools - but it depends!
3. You get the extra memory to use for the buffer cache - this is the one single biggest performance gain as you can seriously decrease physical io ( which is slow ) and get some real performance. You can exceed the size of your physical databases with ram and sql server will still use more.
--------------------------------------
AWE - important point - enabling awe disables dynamic memory - you must set max memory otherwise SQL Server will use all but 256Mb of physical memory - likely this will crash your server.
awe pages though lower memory, as the 3gb switch reduces lower memory using the two together can be bad - what you'll get is increased paging - ideally sql server should not page - if it does you're taking a performance hit.
---------------------------------------
Putting multiple instances on a box just compounds all this and you need to be very careful how you allocate resource.
One option is to install a x64 o/s and run sql in x32 mode - the gain is that you get 4gb of private memory for sql server instead of 2gb. For three instances you'd need to allow for 4GB of private memory for each instance + between 2 and 6gb for the o/s. You should be very aware that sql server can and does use memory other than what you have allocated to it - there's a shed load of stuff which runs out of process.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply