SQL Server Memory Architecture. Myth Busting and how sad is AWE/32 bit.

I have a little picture below to show how memory is broken up in the SQL Server Working set.

Some key Myths to dispel:

a) The Max Memory Setting only sets the target memory for the buffer pool not for the whole of SQL Server working set. So do not set it as high as the MEM you think SQL has to play with, there is formula for determining amount of space to leave for Thread Stacks, but it eludes me ;-)

b) The "Lock pages in Memory" ONLY stops the buffer pool from being paged. Other areas of SQL are fair game so memory pressure may still kill you. Bottom line is Lock Pages in RAM will help EE run smoother but severe external memory pressure is a bad sign. If you lock pages in RAM and set the max server memory too high - I dread to think what could happen.. a nice big buffer pool and no space for threads maybe - who knows...

c)  in 32 bit AWE ONLY can be used for the DB Page Cache. There is a lot of confusion here as sometimes the DB Page Cache is called the "Buffer Pool" (even internally at MS), when it is just one of many components inside the buffer pool.

In fact AWE can actually make matters worse as the non AWE space needs to store a MAP to the AWE memory space as a reference pointer. On a 64 GB system I think this would come to about 512 MB all from the measly 1 GB or less the DB Page Cache has to play with out of non AWE RAM. Now that gotta hurt somewhere..

To make matters worse one place you can really need RAM is the the Query Workspace. This is targeted as 25%-75% of the buffer pool size. All the RAM in the world above 4GB on 32 bit platform isn't going to help here.