SQL Server 2005 Memory Limits and Related Questions
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
https://msdn2.microsoft.com/en-us/library/aa366778.aspx
SQL Server Limits
Memory Supported by the Editions of SQL Server 2005
https://msdn2.microsoft.com/en-us/library/ms143685.aspxUpdated: 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 maximum1
OS maximum1
Developer Edition
OS maximum1
32 TB
Standard Edition
OS maximum1
32 TB
Workgroup Edition
3 GB
N/A2
SQL Server Express Edition
1 GB
N/A2
Evaluation Edition
OS maximum1
OS maximum1
1This edition of SQL Server 2005 will support the maximum memory supported by the operating system.
2This edition of SQL Server 2005 is not available for the 64-bit platform in this release.
Maximum Number of Processors Supported by the Editions of SQL Server 2005
https://msdn2.microsoft.com/en-us/library/ms143760.aspx
Maximum Capacity Specifications for SQL Server 2005
https://msdn2.microsoft.com/en-us/library/ms143432.aspx
Microsoft SQL Server and Intel Hyper-Threading Technology
https://www.microsoft.com/sql/howtobuy/SQLonHTT.doc
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
https://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
https://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
https://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
https://support.microsoft.com/default.aspx?scid=kb;EN-US;895575
[Security Update for RTM]
840987 MS04-032: Security update for Microsoft Windows
https://support.microsoft.com/default.aspx?scid=kb;EN-US;840987
SQL Server 2005 Best Practices Analyzer
https://www.microsoft.com/downloads/details.aspx?FamilyID=da0531e4-e94c-4991-82fa-f0e3fbd05e63&DisplayLang=en
[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
https://support.microsoft.com/default.aspx?scid=kb;EN-US;922658919341 A program that uses structured exception handling may stop responding for several seconds in Microsoft Windows Server 2003 with SP1
https://support.microsoft.com/default.aspx?scid=kb;EN-US;919341
SQL Server Working Set Trim Problems? - Consider...
https://blogs.msdn.com/psssql/archive/2008/03/03/sql-server-working-set-trim-problems-consider.aspx
Bob Dorr
SQL Server Senior Escalation Engineer