confused about memory
-
Hello.
I've read a bit about SQL Server and memory and it seems I have found conflicting data.
Most of our servers have 4 gb of ram.
None of our servers are running SQL Server Enterprise edition (all are standard edition).
Is it true we are only using 2 gb of memory on each server?
Must we include the /3gb switch in the boot.ini to allow our servers to use 3 gb of memory?
Can standard edition even use 3 gb of memory?
-
If you look in BOL (Editions of SQL Server) and select (Maximum Capacity Specifications) scroll to the bottom (Max amount of RAM)
SQL 2k standard can only use 2GB RAM
Hope this helps
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Also, if you're using Windows 2000 Server (not Advanced Server), the /3GB switch will load drivers, etc. into memory like it would in Advanced Server, but applications are still limited to 2 GB. Windows 2000 Advanced Server and Data Center Server do not have this limitation. Neither does any of the Windows XP or Windows 2003 builds.
A description of the 4 GB RAM tuning feature and the Physical Address Extension switch (291988)
K. Brian Kelley
@kbriankelley -
But the key of course is the fact you are using SQL standard edition. You cannot use more than 2GB under any circumstance.
-
also, once you get everything straight from a SQL version and Windows version standpoint, you'll find that the most memory you'll use will be about 3.4 GB of physical memory (if they're dedicated single-instance SQL machines).
this is because of the way windows allocates virtual memory to a process -- each process thinks it has 4 GB of *it's very own* memory to work with, regardless of how much physical memory you have, and leaves the problem of how to make that really happen, to the operating system. the operating system then takes the physical memory + the pagefile, and shuffles/juggles/maps memory around in those spaces so that all applications that ask for memory, are granted memory, and the memory that's most actively used gets dropped in the fastest spots (physical ram) and the memory that's accessed the least is dropped in the slower spots (pagefile). normally, that 4 GB of virtual memory is split into 1/2 - 2GB to "user mode" and 2 GB to "kernel mode". when you specify /3GB on the windows startup, that causes the split to become 3:1. in the case of SQL server, this is significant because the memory that's used for buffering pages of data and processing queries is all allocated out of "user mode" memory, which means that even if you install SQL Enterprise, under normal conditions, SQL will only use a maximum of 2 GB of *virtual* memory for processing and caching data (kindof it's core M.O.), which can only map to a maximum of 2 GB of physical memory. you drop in the /3GB switch, you'll get SQL to use an extra gig of virtual for its buffer pool, which the operating system can map to an extra gig of physical.
"then how do i make productive use of the full 4GB of physical ram on a dedicated SQL machine?" i recently opened a msoft support ticket to answer this question, and i got internally inconsistent answers from tech to tech. the final answer seems to be "it isn't possible". 4GB physical seems to be a magical dead-spot - because of the fact that SQL can't allocate more than 3GB of user-mode memory using the standard windows memory manager, and in my experience, it doesn't have a need for a full GB of kernel mode memory (from what i understand, this memory is used for operating system-level interactions, such as reading/writing files, io ports, etc.) _i'm almost certain at this point that it is *impossible* for a dedicated single-instance sql machine to efficiently make use of exactly 4 GB of physical ram._
PAE & AWE:
Physical Address Extension, Address Windowing Extensions -- PAE makes memory above 4GB available to applications, AWE is the "consumer" of that extended memory within SQL server. One of these two, and i believe it's AWE, has to create it's own memory map, much like a file allocation table, to maintain a list of what extended memory location is being used for what piece of information. this table itself consumes memory, which i think is why you almost never see references to AWE being used for a box with less than 8 GB of physical memory, never for a box that's got less than or equal to 4GB.
/USERVA:
this is new on windows 2003, only allows you to *decrease* the size of the user-mode memory block when you have the /3GB switch enabled. so, for the purposes of attempting to make sql server use more physical memory for its buffer pool, it's not useful.
the bottom line:
our current setup - windows 2003, sql enterprise, /3GB switch in windows, AWE *enabled*. microsoft gave me both answers to the question of whether AWE should be turned on - that this was both necessary and unnecessary. the tech that was most detailed and believable was the one that said it was unnecessary - said that it was only built to use memory from what was provided through PAE, and PAE was only built to address memory over 4GB, ergo there was nothing for AWE to address on my machine. strangely though, there is definitely a difference in free memory shown in task manager (notoriously inaccurate i hear) between when i enable AWE (max ~3.4 GB usage) and when i disable it (max ~2.76 GB usage). so i leave it enabled because it gives me a warm feeling. the difference may be due to the address allocation table i mentioned earlier. if you choose to enable AWE, you need to set the "set working size" config parameter to '0' - these two settings are incompatible, as you'll see in the errorlogs on startup if you enable both. if "set working size" is enabled, AWE will be disabled, regardless of it's config setting.
testing:
i used the following script to test for the max memory allocation sql would commit to:
---------------
declare @eatmem table(a varchar(8000))
insert into @eatmem (a) values (space(8000))
while (1=1)
begin
insert into @eatmem(a)
select top 2048 a from @eatmem
end
--gives you a fairly smooth memory climb, then you'll see it level out somewhere. i'm
-- calling that the top. perhaps someone can comment on whether that's correct or not
---------------
-
This is some information about the memory management in SQL server and also explains some phrases in memory architecture.
Every process in Windows (32 bit) can address up to 232 bytes = 4 GB of memory:
- Application – 2GB
- Kernel – 2GB
- /3GB in boot.ini
– Advanced Edition
– <st1
laceName>Data</st1laceName> <st1laceType>Center</st1laceType>– Given to Application
The physical memory consists of these three parts:
- Buffer Pool(BPool)
– Goal: Maximize Bpool use , minimize db files I/O
– Can grow or shrink dynamically within configuration parameters (AWE not dynamic)
– Based on RAM – reserve Bpool at startup
– If no upper limit set for max server memory, then BPool = size of RAM – 5 MB (4-10MB)
– Allocations in BPool in 8 KB pages
- MemToLeave(MTL)
– What is left after you subtract BPool from Total Virtual Application Memory
– 2 GB (3GB) – BPool – Stack space = MemToLeave
– XPs, sp_OA*, Linked Server, EXE/DLLs are loading in to this area on memory.
– If RAM>2GB, size 256 MB (SQL2K), 128 MB (SQL7.0)
– Use –g startup prameter to change
- Stack space – 512 KB per worker thread(SS)
– 255 worker thread * 512 KB = 128 MB
This table shows the actual memory space for each part based on available physical Ram.
In this table the AWE is NOT enabled.
RAM
App VM
Bpool
MTL+SS
-g
256 MB
2 GB
250 MB
1.75 GB
Not set
1 GB
2 GB
995 MB
1.05 GB
Not set
1.61 GB*
2 GB
1.61 GB
384 MB
Not set
2 GB
2 GB
1.61 GB
384 MB
Not set
2 GB
2 GB
1.5 GB
512 MB
-g384
4 GB
2 GB
1.61 GB
384 MB
Not set
4 GB
/3GB
2.61 GB
384 MB
Not set
8 GB
/3GB
2.61 GB
384 MB
Not set
Address Windowing Extensions (AWE) API is available in:
– Windows 2000 Advanced – up to 8 GB
– Windows 2000 Data Center - up to 64 GB
– Requires /PAE Intel chip support
- Mapping/unmapping a “view” or “window” of physical memory pages into the process virtual address space (2 / 3 GB)
- sp_configure ‘awe enabled’ set to 1
– It is important that ‘max server memory’ is Always set with AWE otherwise the OS or other applications will have problem with short memory.
This is How AWE works if you have physical memory more than 8GB.
In this table the AWE is enabled.
RAM
App VM
Max srv Memory
Bpool
MTL+SS
8 GB
2 GB
7.5 GB
7.5 GB
384 MB
16 GB
2 GB
15.7 GB
15.7 GB
384 MB
16 GB
/3GB
15.7 GB
15.7 GB
384 MB
40 GB
/3GB
39.2
16 GB
384 MB
40 GB
2 GB
39.2
39.2 GB
384 MB
64 GB
2 GB
63.3 GB
63.3 GB
384 MB
As you can see here enabling the AWE will have the best performance for servers with more than 8GB.
-
I don't know where you got that data from but I can asure you that it is impossible to use /3GB when the OS has to manage more than 16GB AWE
* Noel -
Microsoft Support! I paid for the information. AND implemented AND it works.
Oh. I contracted there too.
-
Are you saying that
FROM BOL: (Managing AWE Memory)
...
In order to allow AWE to use the memory range above 16 GB, be sure the /3gb parameter is not in the boot.ini file. If it is, Windows 2000 will be unable to address any memory above 16 GB.
IS A LIE ?
>>Oh. I contracted there too<<
Couldn't care less
* Noel -
If you have > 16 GB of memory you can toggle the /3GB switch. Problem is, the OS won't see beyond 16 GB.
Quote:
" The preceding information is valid for programs that run when the /3GB switch is used. A program that requests 3 GB of memory is more likely to be able to have more of its memory remain in physical memory rather than be paged out. This increases the performance of programs that are capable of using the /3GB switch. The exception is when the /3GB switch is used in conjunction with the /PAE switch. In this case, the operating system does not use any memory in excess of 16 GB. This behavior is caused by kernel virtual memory space considerations. Thus, if the system restarts with the /3GB entry in the Boot.ini file, and the system has more than 16 GB of physical memory, the additional physical random access memory (RAM) is not used by the operating system. Restarting the computer without the /3GB switch enables the use of all the physical memory."
From:
Large memory support is available in Windows 2000 and Windows Server 2003 (283037)
K. Brian Kelley
@kbriankelley -
>>If you have > 16 GB of memory you can toggle the /3GB switch. Problem is, the OS won't see beyond 16 GB.<<
Which again, proofs my point
* Noel -
Yup, we're in agreement. I was backing up your point.
If MS Support is saying something different, I'd love to see a reference. Because if they are, it contradicts what the KB says.
K. Brian Kelley
@kbriankelley -
There is an article on this in the March 2005 Sql Server Magazine:
http://www.windowsitpro.com/Windows/Article/ArticleID/45156/45156.html
The article states that Microsoft's recommendations have recently changed, hence the contradictions out there.
New recommendation is use the /3GB switch only if physical memory is 12GB or less.
-
I don't think you guys are reading. The configurations are spelled out plainly in the reply.
The data I supplied was a discussion I had with support AWE vs. Not enabling AWE on SQL Server and the use of additional memory. AWE is on SQL Server, and PAE is on the server and configured in the boot.ini file of the server.
ex.
[boot loader]
timeout=2
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise, PAE" /fastdetect /3GB /PAE
sp_configure
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
awe enabled 0 1 1 1
Now you should be able to do some test with memory configurations.
"
Address Windowing Extensions (AWE) API is available in:
– Windows 2000 Advanced – up to 8 GB
– Windows 2000 Data Center - up to 64 GB
– Requires /PAE Intel chip support
- Mapping/unmapping a “view” or “window” of physical memory pages into the process virtual address space (2 / 3 GB)
- sp_configure ‘awe enabled’ set to 1
– It is important that ‘max server memory’ is Always set with AWE otherwise the OS or other applications will have problem with short memory.
This is How AWE works if you have physical memory more than 8GB. "
I stress test because servers are as temperamental and unique as the people using this thread.
-
To bring this all back on topic...
You cannot access > 2 GB unless you are running SQL Server 2000 Enterprise Edition. Therefore, running Standard Edition, that's your limit unless you upgrade SQL Server. If you do plan on upgrading SQL Server, make sure your OS supports accessing memory greater than 2GB for the app.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply