First published on MSDN on Dec 22, 2009
So what is part II about? It is about a change in Standard 64 bit Editions of SQL Server 2005 and 2008. This change implements the support of AWE APIs and “Lock Pages in Memory” privilege by these editions. It has been already advertised by Bob Ward and worth to be known especially by the DBAs who have seen their Standard Edition of SQL Server 2005 and 2008 being paged out from time to time without immediate solution or workaround.
Starting with this Cumulative Updates:
- Cumulative update package 2 for SQL Server 2008 Service Pack 1
- Cumulative update package 4 for SQL Server 2005 Service Pack 3
The 3 available memory allocation types now driven by the following condition are:
- MMLarge:
- Enterprise edition
- Large page support
- Lock Page In Memory
- TF 834 enabled (c.f. http://support.microsoft.com/kb/920093 )
- MMAwe
- Enterprise Edition
- Lock Page In Memory
- Standard Edition with TF 845 enabled
- TF 835 not enabled
- MMConventional
- Standard edition
More Information
For the trimming issue there were several possible workarounds.
- Operating System fix:
- A Windows 2003 fix can be found here .
- Driver fixes:
- An extensive troubleshooting guide can be found here .
Again none of these solutions is easily applicable from a SQL Server perspective.
Running SQL Server on Windows 2008
Windows 2008 is less subject to memory trimming issues because of an improved contiguous memory allocation management. Related links can be found here:
- How to reduce paging of buffer pool memory in the 64-bit version of SQL Server
- SQL Server Working Set Trim Problems? - Consider...
Memory Model naming conventions
§ The three different memory model names starting with double M which stands for… Memory Model of course, i.e. Memory Model AWE, Memory Model Large and Memory Model Conventional.
Lionel Pénuchot
Senior PFE, Microsoft France
