SQL Server memory models (Part II)

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. https://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:

o A Windows 2003 fix can be found here.

§ Driver fixes:

o Fix of Integrated Lights-Out Management (iLO) Interface cpqcidrv.sys driver you can find here

o Fix of Broadcom driver bxvbda.sys by disabling TCP Chimney you can find the steps here.

§ 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:

o How to reduce paging of buffer pool memory in the 64-bit version of SQL Server

o 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