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.aspx

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 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;922658

919341 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