Not so long ago, I had written a blog post for SQL Server myths related to Setup & Service startup myths for T-SQL Tuesday which was hosted by Sankar Reddy (Blog | Twitter). The round-up of the T-SQL Tuesday posts had some awesome content where renowned SQL Community members debunked common SQL Misconceptions. Read the round-up here.

I found that that this was quite a compelling topic for a series and decided to do a Part II for SQL Myths around memory related misconceptions. SQL Memory by itself is a murky topic and there are quite a few urban legends that are so well imbibed in people’s memories that we have to battle against them to get users to see light at the end of the tunnel. So, here goes another round of debunking some more misconceptions.

AWE needs to be enabled on 64-bit instances for SQL Server
I think everyone who has posted an answer for this question on MSDN forums has probably gone tired of saying the same thing over and over again. This configuration option is required for 32-bit instances of SQL Server only to enable the instance to benefit from the Address Windowing Extension. AWE option on 64-bit instances has no meaning. This is not required and basically does nothing. Please refer the following blog post more details:
http://blogs.msdn.com/b/slavao/archive/2006/03/13/550594.aspx

System.OutOfMemoryException encountered while executing a query from SSMS is not a Database Engine Out-of-Memory condition
I have seen multiple suggestions on forums for reducing Max Server Memory. This error is a .NET exception telling the user that an OOM condition occurred. The database engine is not a managed application. It was coded in native language and hence, wouldn’t throw a .NET OOM error. I have written about this in an earlier blog post: System.OutOfMemoryException for Management Studio

Windows Task Manager will tell me SQL Server’s memory usage
Another urban legend that CSS deals with day in and day out. The “Monitoring Memory Usage” topic in SQL Server Books Online talks about how to monitor SQL Server memory usage. None of the tools you typically use to inspect application memory use (Task Manager, Perfmon/Sysmon, etc.) show the amount of AWE memory used by individual processes. There’s no indication of the amount of AWE memory used by each process, nor is this memory included in the working set size reported for a given process. Reference: http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

Max Server Memory will limit the SQL Server memory
This is true for only the SQL Server Buffer Pool. Max Server Memory setting is used to control the size of the Buffer Pool only. For a 64-bit instance, the non-BPool region can grow to a maximum of Total Physical Memory – (Max Server Memory + Memory used by Windows & other applications). I had blogged about this in the past in my post: Non Bpool region on 64bit SQL instances may be larger than you think

SQL Server eats up all the available memory on the box
SQL Server is a server application. Ideally, you should have a dedicated server for a SQL instance that has a high number of database transactions. If that is not the case, then the Max Server Memory needs to be capped after taking into account the memory requirements for Windows, SQL Server and the other applications on the box. SQL Server (especially 64-bit) is designed to not back off it’s memory footprint once it grows unless it is asked to because of a low memory notification received from the Operating System. The thumb rule of leaving just 2GB for the OS and other applications may not be enough when you have File Copies, SSRS, SSIS and SQL Database Engine running on the same instance. SQL Server 2005 and above is designed to react to low memory notifications sent out by Windows and scale back it’s memory usage as required.
Reference: http://msdn.microsoft.com/en-us/library/ms178145.aspx

Virtual Address Space – Isn’t that an excuse?
Virtual Address Space defines the amount of memory that can be addressed by any application and the limiting factor is always the processor architecture: 32-bit or 64-bit. 32-bit Windows can reference upto 4GB (2³²) and 64-bit can reference a maximum of 8TB (2⁶⁴) depending on the Windows edition. The addressing limitations on a 64-bit instance is limited to the availability of your Physical RAM which is an exact opposite of the story of a 32-bit server. With Physical Address Extension enabled on the Windows servers, Windows can use more than 4GB of memory and SQL Server (or any other Windows application if coded correctly) can take advantage of this by using AWE APIs. If you are falling prey to VAS limitations, then you either have a large VAS consumer or your workload is not the most optimal workload for 32-bit architecture! If you are facing a Virtual Address crunch on your SQL instance, then follow the steps mentioned in the post to work towards determining the root cause of the problem: Who is eating up the Virtual Address space on your SQL Server
Reference: http://technet.microsoft.com/en-us/library/ms187499(SQL.90).aspx

I can change –g to whatever I want!
Absolutely not! The hard coded upper limit for –g is 1024GB. Whenever you increase your MemToLeave on a 32-bit machine, you can decreasing the SQL Serve Buffer Pool memory proportionately. Enabling this for a 64-bit instance is bit of a moot-point. So increasing –g is just a workaround and sometimes it might lead you to face BPool pressure on the server. I had documented the layout of the buffer pool for a 32-bit instance in an earlier blog post: SQL Server Memory Basics
Reference: http://msdn.microsoft.com/en-us/library/ms190737(v=SQL.90).aspx

I am Smart! I will enable /3GB and then increase –g. All problems solved!
Well, I would say you are too smart for your own good. For systems with above 16GB of RAM, this can prove to be counter-productive. This will only cause a lack of available System PTEs on the Windows server and create more problems than it actually solves. From “Using AWE” topic in SQL Server Books Online:

If there is more than 16 GB of physical memory available on a computer, the operating system requires 2 GB of virtual address space for system purposes and therefore can support only a 2 GB user mode virtual address space. For the operating system to use the memory range above 16 GB, be sure that the /3gb parameter is not in the Boot.ini file. If it is, the operating system cannot use any physical memory above 16 GB.

Lock Pages in Memory is the next best thing after sliced bread!
How many times have we heard this: “Enable LPIM for the SQL service account and everything will be alright”! Everything will be alright only when you have taken into consideration the memory requirements for the SQL Server instance(s) on the box, other applications and Windows and then capped the Max Server Memory. Without that enabling LPIM for the SQL Service account is as good riding a bike on the freeway without a helmet. It’s all smooth sailing till disaster strikes!

Hope the above points help clear out some urban legends regarding SQL Server Memory concepts and next time during a coffee break or a water cooler discussion, the above points help you convince a *misguided SQL Server expert*!!