CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

SQL Server 2005 Memory Limits and Related Questions

SQL Server 2005 Memory Limits and Related Questions

  • Comments 3

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

Memory Supported by the Editions of SQL Server 2005
http://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.

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


Bob Dorr
SQL Server Senior Escalation Engineer

Leave a Comment
  • Please add 3 and 8 and type the answer here:
  • Post
  • WHAT ARE THE LIMITATIONS OF SQL SERVER 2005 CAN YOU PLIZZ HELP ME

  • Could you confirm if SQL Server 2005 Standard Edition x64 bit memory limit is really 32 TB?

  • What are the limitations for Windows server 2003 SP2 32 bit ENT edition

Page 1 of 1 (3 items)