| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2005-10-19 : 12:48:55
|
| I am configuring a server with Windows Server 2003 Standard Edition and SQL Server 2000 Enterprise Edition. The server has 4GB memory. From what I have read the AWE/PAE switch only works with Advanced Server or Datacenter Editions, which are not an option for me. What is needed for SQL Server to use 3GB of the memory for 2003 SE? Thanks, Dave |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-19 : 13:03:40
|
| The /3GB switch in the boot.ini will allow enterprise edition to address up to 3GB RAM. I think you can do some searching in the forums for more discussion on this. I think Kristen has a post with frequently answered questions with this information also.-ec |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-19 : 13:10:48
|
| I've done some searching, which is making me even more confused. I've read where the /3GB switch is supported for Windows Server 2003 Standard Edition, however it may lead to problems with the kernel. I've tried to find Microsoft documentation explaining the issue, but no luck.I've also read some comments indicating the switch is not needed with 2003 Standard Edition, which I'm finding hard to believe.Thanks, Dave |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-19 : 13:12:17
|
quote:
Originally posted by DBADave
I've done some searching, which is making me even more confused. I've read where the /3GB switch is supported for Windows Server 2003 Standard Edition, however it may lead to problems with the kernel. I've tried to find Microsoft documentation explaining the issue, but no luck.I've also read some comments indicating the switch is not needed with 2003 Standard Edition, which I'm finding hard to believe.Thanks, Dave
we just had a recent thread on this subject. I'll try to locate it and link it here.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-19 : 13:21:50
|
| Thanks eyechart. That's the thread that confused me along with another thread (different forum) about how the combination of Windows Server 2003 SE and SQL Server 2000 EE will cause SQL Server to use only 2GB, even with the switch set. I'm not sure what to believe.Dave |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-19 : 14:37:19
|
quote:
Originally posted by DBADave
Thanks eyechart. That's the thread that confused me along with another thread (different forum) about how the combination of Windows Server 2003 SE and SQL Server 2000 EE will cause SQL Server to use only 2GB, even with the switch set. I'm not sure what to believe.Dave
Derrick has the information in his post that you want to see though. SQL 2K is supported and so is Exchange in this configuration. -ec |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-20 : 17:47:52
|
| Here's an update.I've been on the phone with Microsoft the past two days trying to get an explanation of how Windows Server 2003 Standard Edition can allow SQL Server Enterprise Edition to use 3GB of physical memory, but the three technicians I've spoken with (2 windows & 1 SQL Server) appear just as confused as me. The 3GB switch will only allow SQL Server to use "virtual memory". I want to use 3GB of the physcial memory to avoid accessing the disk for memory requests.I've been told Windows Server 2003 is PAE enabled (assuming hot-add memory is used ), but then have been told PAE only applies to more the 4GB of physical memory and that only 2003 EE and Data Center are automatically enabled. I've also read where AWE only applies to more then 4GB. They currently have me on hold trying to determine if SQL Server can use 3GB of physical memory. Do you have anything to add regarding 3GB of physical memory or any links that explain if SQL Server can or cannot use 3GB of physical memory on a 4GB server (Windows Server 2003 SE)? I appreciate the help. Dave |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-20 : 17:59:08
|
quote:
Originally posted by DBADave
Here's an update.I've been on the phone with Microsoft the past two days trying to get an explanation of how Windows Server 2003 Standard Edition can allow SQL Server Enterprise Edition to use 3GB of physical memory, but the three technicians I've spoken with (2 windows & 1 SQL Server) appear just as confused as me. The 3GB switch will only allow SQL Server to use "virtual memory". I want to use 3GB of the physcial memory to avoid accessing the disk for memory requests.I've been told Windows Server 2003 is PAE enabled (assuming hot-add memory is used ), but then have been told PAE only applies to more the 4GB of physical memory and that only 2003 EE and Data Center are automatically enabled. I've also read where AWE only applies to more then 4GB. They currently have me on hold trying to determine if SQL Server can use 3GB of physical memory. Do you have anything to add regarding 3GB of physical memory or any links that explain if SQL Server can or cannot use 3GB of physical memory on a 4GB server (Windows Server 2003 SE)? I appreciate the help. Dave
I have been less than impressed with microsoft's support for the last year. The current first line support is located overseas and they have very little experience. We have had many discussions with our TAM over the bad support and the fact that we pay even more for it now then we did before. For fun you should call in for support on IA64 release of windows or SQL Server, that is a fun experience. We were told (erroneously of course) that the OS was still beta and completely unsupported!Anyway, it seems you are getting bunk information from them. Use the /3GB switch in standard edition win2k3 it is supported with EE release of SQL Server. Try it in a test system first if you are concerned.-ec |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-20 : 18:08:40
|
| Still on hold with Microsoft....Can you confirm if the 3GB switch is using physical memory or virtual memory?Thanks again, Dave |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-20 : 18:28:53
|
quote:
Originally posted by DBADave
Still on hold with Microsoft....Can you confirm if the 3GB switch is using physical memory or virtual memory?Thanks again, Dave
this switch changes the virtual address space for a process from 2GB to 3GB. This can be either virtual or physical memory, but don't get confused by this.If you have the available physical memory, you will be able to address up to 3GB. -ec |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-20 : 21:46:40
|
| EC,Sounds good to me. By the way, I'm still waiting for a Microsoft response. I had to hang-up so to catch a train. I asked them to send me an email with the rules/restrictions associated with Windows Server 2003 Standard Edition and /3GB, PAE & AWE, but so far no email. Now that's money well spent.Dave |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-21 : 10:15:05
|
| Here is an update. The information below comes from a Microsoft Senior Support rep for Windows Server 2003. I received this information after speaking with 4 level 1 MS support reps, none of which fully understood /3GB, PAE and AWE. I hope this information helps someone./3gb switch is a combination of physical and paged memory, it simply allows an /3gb aware application to allocate up to 3GB of virtual memory. If you want to dedicate chunks of physical memory, increase the physical memory and use /PAE and configure SQL to use AWE memoryIf you are running Windows Server 2003 Standard Edition, you are not able to increase physical memory above 4GB at this time.With that limitation, IF you need more than 2 GB of memory, then the /3gb switch is the only option. With the 3GB switch, use the /userva=2950 to give some of the memory back to the kernel memory, specifically to the Free System Page table entries.Since /3GB reduces kernel address space by 1 GB, it is always advisable to change this configuration only if there is evidence of memory pressure. We always advice our customers to test the memory configuration changes to see what works best for their environment. The following are some of the most common errors we see reported in the SQL error log which might indicate either MemToLeave or Bpool memory pressure.WARNING: Clearing procedure cache to free contiguous memoryWARNING: Failed to [reserve | commit] contiguous memory Error 17802/Unable to spawn xxx threadError 701 - There is insufficient system memory to run this queryError 7399 - OLE DB provider <provider> reported an error. The provider ran out of memory.Error 8645 - A time out occurred while waiting for memory resources to execute the query. Rerun the query Error 8651 - Could not perform the requested operation because the minimum query memory is not available. Decrease the configured value for the 'min memory per query' server configuration option. Error 8902 - Memory allocation error during DBCC processing. The following two articles explain various memory options in great detail which might be helpful: Inside SQL Server 2000's Memory Management Facilities: <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp> How to configure memory for more than 2 GB in SQL Server: <http://support.microsoft.com/default.aspx?scid=KB;%5bLN%5d;274750> Important Notes:/3GB vs. AWE (from Inside SQL Server 2000's Memory Management Facilities article)The ability to increase the private process address space by as much as 50 percent via application memory tuning is certainly a handy and welcome enhancement to Windows memory management facilities; however, the Windows AWE facility is far more flexible and scalable. As I said earlier, when you increase the private process address space by a gigabyte, that gigabyte comes from the kernel mode address space, which shrinks from 2GB to 1GB. Since the kernel mode code is already cramped for space even when it has the full 2GB to work with, shrinking this space means that certain internal kernel structures must also shrink. Chief among these is the table Windows uses to manage the physical memory in the machine. When you shrink the kernel mode partition to 1GB, you limit the size of this table such that it can manage a maximum of only 16GB of physical memory. For example, if you're running under Windows 2000 Data Center on a machine with 64GB of physical memory and you boot with the /3GB option, you'll be able to access only 25 percent of the machine's RAM-the remaining 48GB will not be usable by the operating system or applications. AWE also allows you to access far more memory than /3GB does. Obviously, you get just one additional gigabyte of private process space via /3GB. This additional space is made available to applications that are large-address aware automatically and transparently, but it is limited to just 1GB. AWE, by contrast, can make the entirety of the physical RAM that's available to the operating system available to an application provided the application has been coded to make use of the AWE Win32 API functions. So, while AWE is more trouble to use and access, it's far more flexible and open-ended.This isn't to say that there aren't situations where /3GB is preferable to AWE-there certainly are. For example, if you need more space for memory allocations that cannot reside in AWE memory (thread stacks, lock memory, procedure plans), you may find that /3GB is a better fit. Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server 2003 The maximum amount of physical memory addressable by a 32-bit addressing mode is 4 GB. All processors based on the IA-32 architecture that begin with the Intel Pentium Pro, support a new 36-bit physical addressing mode known as Physical Address Extension (PAE). PAE allows up to 8 GB of physical memory on Windows 2000 Advanced Server and up to 32 GB of physical memory on Windows 2000 Datacenter Server. This is because the tested memory limit on Windows 2000 Datacenter Server is 32 GB. The PAE mode kernel requires an Intel architecture processor, Pentium Pro, or later and either Windows 2000 Advanced Server or Windows 2000 Datacenter. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: 268230 <http://support.microsoft.com/kb/268230> Scaling out versus scaling up with Intel Physical Addressing Extensions (PAE) SQL Server 2000· Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can use the PAE and AWE. · The PAE use requires Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server 2003.· To allow AWE to use the memory range above 16 GB on Windows 2000 Data Center, make sure that the /3GB switch is not in the Boot.ini file. If the /3GB switch is in the Boot.ini file, Windows 2000 may not be able to address any memory above 16 GB correctly.· Windows NT 4.0 Enterprise Edition does not support the AWE memory architecture model so AWE support is not available. SQL 7.0· AWE isn’t supported in SQL 7.0· SQL Server 7.0 Enterprise edition can use /3GB· The extended memory option in SQL Server 7.0 is not available on Windows 2000 Advanced Server or Windows Datacenter.About 3GB (4 GT) (from http://support.microsoft.com/kb/171793):User-mode Address Selection: When 4GT is enabled, the highest bit of a virtual address cannot be used to differentiate user-mode addresses from kernel-mode addresses. Memory Allocation Issues: Some dynamic link library (DLL) files load near the 2 GB boundary; therefore, there is a region of the 2 GB space in which contiguous memory cannot be allocated using VirtualAlloc. Effects Visible in Kernel Mode: Kernel-mode code can no longer assume the user/kernel boundary is at 0x80000000 or at any other number. Code that uses ProbeForRead or ProbeForWrite macros must be rebuilt using new headers that no longer contain assumptions about kernel space starting at 0x80000000. If you restart with the 3 GB switch, also known as "4 Gig tuning," the amount of non-paged pool is reduced to 128 MB from 256 MB.From http://support.microsoft.com/kb/247904/EN-US/The two types of data that share a portion of this 2 GB address area are Paged Pool allocations, and kernel stack allocations. If the operating system runs out of space in one of those areas, the other area cannot donate space to it, and programs may begin to encounter unexpected errors. Therefore, when you encounter a Windows 2000-based computer that is experiencing unexpected errors or an inability to accept new logins, and the computer does not have some other resource limitation such as Central Processing Unit (CPU) or disk bottlenecks, it is highly likely that the Paged Pool or System PTE areas are becoming low on disk space. Because, by default, the System PTE area is sized to be as large as possible on a computer with Terminal Services enabled, the limitation will usually be due to insufficient Paged Pool address space. In this case, 3GB will not be a good idea. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-21 : 10:19:30
|
| nice post, thanks for the update Dave.-ec |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-24 : 23:56:02
|
| ec,I tried the 3GB switch, but during testing noticed Target Server Memory was much higher then Total Server Memory. I also noticed a fairly large amount of paging. I'm not sure how these counters are impacted by the 3GB switch, given how it uses "virtual memory". Is this something you've seen before?Here is what I did.Modified boot.ini to include /3GB /userva=2950sp_configure "max server memory", 2950When I pulled up Task Manager I noticed SQL Server was only using 950MB, which is much lower then the normal 1.68GB prior to using /3GB.Any thoughts?Dave |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-10-25 : 10:06:27
|
quote:
Originally posted by DBADave
ec,I tried the 3GB switch, but during testing noticed Target Server Memory was much higher then Total Server Memory. I also noticed a fairly large amount of paging. I'm not sure how these counters are impacted by the 3GB switch, given how it uses "virtual memory". Is this something you've seen before?Here is what I did.Modified boot.ini to include /3GB /userva=2950sp_configure "max server memory", 2950When I pulled up Task Manager I noticed SQL Server was only using 950MB, which is much lower then the normal 1.68GB prior to using /3GB.Any thoughts?Dave
Really nice post. I'll need to spend some time looking it over in detail. I think eyechart pointed you to the wrong post in my thread. Franco had found some good stuff in an IBM article:quote:
Originally posted by franco
Hi SQLServerDBA_Dan,I found a very interesting note in a RedBooks Paper from IBM:Tuning Windows Server 2003:"The /3GB switch works for all version of Windows Server 2003, but you should use it only when runnin Enterprise or Datacenter edition.Standard Edition can allocate to user-mode application at most 2GB.If the /3GB switch is used in the Boot.ini file, then the privileged-mode kernel is restricted to 1GB of addressable memory without the corresponding increase of user-mode applications.This effectively means 1GB of address space lost."HTHFranco
Maybe you are seeing the paging because you actually can't increase the physical memory that SQL2kEE can use on Win2k3SE? LOL. Kinda sad when I trust IBM's advice on a MS product over that of Microsoft's.  Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-25 : 10:23:42
|
| I have another call into Microsoft to see if they can explain the problem. The odd thing is I'm now seeing in Task Manager that SQL Server is using 2.56GB of memory. I'm guessing the nightly batch processing requires more memory than the OLTP application. But I would have thought SQL Server still should have used more then 950MB, especially given how Target Server Memory was greater then Total Server Memory. If Target Server Memory is indicating how much memory SQL Server would like to have, then why didn't it allocate more then 950MB? This is what I get for being talked into installing 2003 SE instead of 2003 EE. Arghhhh!!!!Dave |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-25 : 11:52:33
|
quote:
Originally posted by DBADave
I have another call into Microsoft to see if they can explain the problem. The odd thing is I'm now seeing in Task Manager that SQL Server is using 2.56GB of memory. I'm guessing the nightly batch processing requires more memory than the OLTP application. But I would have thought SQL Server still should have used more then 950MB, especially given how Target Server Memory was greater then Total Server Memory. If Target Server Memory is indicating how much memory SQL Server would like to have, then why didn't it allocate more then 950MB? This is what I get for being talked into installing 2003 SE instead of 2003 EE. Arghhhh!!!!Dave
maybe the cache was just warming up. I don't think SQL is going to do disk IOs for nothing just to load up the buffer cache, even though it has a target that is higher than the total. Now that the system is hot, you see that the cache is where it should be.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-25 : 11:56:09
|
quote:
Originally posted by SQLServerDBA_DanReally nice post. I'll need to spend some time looking it over in detail. I think eyechart pointed you to the wrong post in my thread. Franco had found some good stuff in an IBM article:quote:
Originally posted by franco
Hi SQLServerDBA_Dan,I found a very interesting note in a RedBooks Paper from IBM:Tuning Windows Server 2003:"The /3GB switch works for all version of Windows Server 2003, but you should use it only when runnin Enterprise or Datacenter edition.Standard Edition can allocate to user-mode application at most 2GB.If the /3GB switch is used in the Boot.ini file, then the privileged-mode kernel is restricted to 1GB of addressable memory without the corresponding increase of user-mode applications.This effectively means 1GB of address space lost."HTHFranco
his box has 2.56GB allocated to SQL Server right now, so that IBM quote seems to be inaccurate.-ec |
|
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2005-10-26 : 05:48:50
|
quote:
Originally posted by DBADave
I have another call into Microsoft to see if they can explain the problem. The odd thing is I'm now seeing in Task Manager that SQL Server is using 2.56GB of memory. I'm guessing the nightly batch processing requires more memory than the OLTP application. But I would have thought SQL Server still should have used more then 950MB, especially given how Target Server Memory was greater then Total Server Memory. If Target Server Memory is indicating how much memory SQL Server would like to have, then why didn't it allocate more then 950MB? This is what I get for being talked into installing 2003 SE instead of 2003 EE. Arghhhh!!!!Dave
Dave,maybe this is already done, but this is just something else to check:Make sure that the user account the SQL Server service is runningon is added to "Lock Page in Memory policy" of the "User RightsAssignments" in "Local Security Policy" of the computer. Cheers.Franco |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-26 : 08:51:45
|
| Franco,I may be wrong, but I believe that only applies to the AWE memory option.Using AWE MemoryTo use AWE memory, you must run the SQL Server 2000 database engine under a Windows 2000 account that has been assigned the Windows 2000 lock pages in memory privilege. I'm going to turn off the /3GB option today. Our testing has revealed slower application performance since we upgraded to SQL Server EE and turned on /3GB. I can't see the version of SQL Server causing an application to run slower, so I'm assuming it's related to the /3GB switch and kernel memory usage.Thanks, Dave |
|
|
|
Next Page
|
3GB Memory and Windows Server 2003 SE