First published on MSDN on Apr 05, 2008
I had a very nice conversation this week with one of the blog readers. The question related to adding more memory to their server. It lead to an exchange of various links that I think you might find handy. We discussed the operating system limitations and the SQL Server limitations. Each of these are outlined in different locations.
Operating System Limits
The operating system establishes installed memory limitations by SKU. SQL Server is limited by what the operating system presents.
Memory Limits for Windows Releases
http://msdn2.microsoft.com/en-us/library/aa366778.aspx
SQL Server Limits
Updated:
15 September 2007
The following table specifies the maximum memory support for each edition of Microsoft SQL Server 2005.
SQL Server 2005 edition Maximum memory supported (32-bit) Maximum memory supported (64-bit)
Enterprise Edition
|
OS maximum
1
|
OS maximum
1
|
Developer Edition
|
OS maximum
1
|
32 TB
|
Standard Edition
|
OS maximum
1
|
32 TB
|
Workgroup Edition
|
3 GB
|
N/A
2
|
SQL Server Express Edition
|
1 GB
|
N/A
2
|
Evaluation Edition
|
OS maximum
1
|
OS maximum
1
|
1
This edition of SQL Server 2005 will support the maximum memory supported by the operating system.
2
This edition of SQL Server 2005 is not available for the 64-bit platform in this release.
PAE Bug
In the process of discussing the memory upgrade I mentioned that there is a known set of Windows PAE related bugs that can lead overall system instability. In fact, I would go as far to say that I would even consider running SQL Server without the fixes. Microsoft SQL Server Support has helped dozens and dozens of customers that encountered problems because of these bugs and limits how much debugging we will do on a system that has /PAE enabled or the operating system supports hot add memory when the fixes are not applied. We have found that in many cases it is wasted effort for the customer and Microsoft to attempt to debug such a random natured problem.
These problems range from unexpected behaviors, exceptions to database corruption and data loss. The bug looks like the system has a memory chip that is failing and returning incorrect data. The bug causes incorrect physical page is mapped into the virtual address space of any process.
Bob Ward and I have both mentioned this issue during SQL PASS and other presentations for a couple of years and we have added rules to the SQL Server 2005 BPA utility to flag systems that are unprotected from this bug.
If you are running Windows 2000 or Windows 2003 RTM you should update your server.
Windows 2000 fixes
[Post Win 2000 SP4]
838647 Access violations when you use the /PAE switch in Windows 2000
http://support.microsoft.com/default.aspx?scid=kb;EN-US;838647
[Post Win 2000 Sp3]
885272 You receive a "Stop 0x0000001a" Stop error message on your Windows 2000 Server-based computer that has Physical Address Extensions (PAE) enabled
http://support.microsoft.com/default.aspx?scid=kb;EN-US;885272
Windows 2003 fixes
[Included in Win 2003 SP1]
834628 Data is corrupted when PAE is enabled on a Windows Server 2003-based computer
http://support.microsoft.com/default.aspx?scid=kb;EN-US;834628
[Included in Win 2003 SP1]
895575 A process that runs in the Physical Address Extension (PAE) kernel may experience memory corruption in Windows Server 2003
http://support.microsoft.com/default.aspx?scid=kb;EN-US;895575
[Security Update for RTM]
840987 MS04-032: Security update for Microsoft Windows
http://support.microsoft.com/default.aspx?scid=kb;EN-US;840987
SQL Server 2005 Best Practices Analyzer
http://www.microsoft.com/downloads/details.aspx?FamilyID=da0531e4-e94c-4991-82fa-f0e3fbd05e63&...
[APR 16 2008 REVISION - Bob Dorr and Suresh Kandoth]
The original post indicated you needed to move to Windows 2003 SP2 to avoid the PAE issue. This was incorrect, I was thinking of some of the other issues we have encountered and I would still suggest Windows 2003 SP2 if you can't go to Windows 2008.
922658 SQL Server 2000 or SQL Server 2005 may temporarily stop responding on a Windows Server 2003 Service Pack 1-based computer
http://support.microsoft.com/default.aspx?scid=kb;EN-US;922658
919341 A program that uses structured exception handling may stop responding for several seconds in Microsoft Windows Server 2003 with SP1
http://support.microsoft.com/default.aspx?scid=kb;EN-US;919341
SQL Server Working Set Trim Problems? - Consider...
http://blogs.msdn.com/psssql/archive/2008/03/03/sql-server-working-set-trim-problems-consider.a...
Bob Dorr
SQL Server Senior Escalation Engineer