SQL Server - Instance level memory settings and usage

After several pure T-SQL posts, today I thought to write about a post which discuss more about the MS SQL Server instance level properties and try to shed more light on it.

In this post I'll talk about the most commonly used property i.e. memory settings. Although many of us have seen this option and many are very much familiar but let us revisit it to check whether we are using it or understand it the way it should be.

Though on memory page we see five option but in this post we will talking more about only three, which are "AWE", "maximum server memory" and "minimum server memory".

First up is "AWE" which is short for "Address Windowing Extensions". It is special API built when the 64 Bit architecture was introduced and applications were still in phase where they were need to be fully ported on it. As most of us know that 32 Bit application can use only of 2-3GB of physical memory (depending on the OS option used like /3GB or /PAE) due to limitation forced by address space and this limit was removed with introduction of 64 Bit architecture. 

So, Microsoft introduced a new API known as AWE where a 32 Bit application can use more than 2 or 3GB of RAM. To use this option in full effect, you also have to give SQL Server Account "Lock Pages in Memory" rights because the memory allocated by AWE API have a higher privilege and cannot be easily claimed back by OS.

Although, if we go by the purpose, AWE is not required when you already have 64 Bit system running with 64 Bit version of SQL Server, but you can still enable it if you want your pages to stay a bit more longer in the memory because pages are allocated in the same way by AWE API as it is does for a 32 Bit SQL Server, which again have higher privileges. For a 64 bit system this is also known as "locked pages".

To avoid SQL Server from consuming too much physical memory on server (either due to AWE or on 64 Bit system) we have second option i.e. "Maximum Server Memory". By using this option we can define the upper limit of physical memory SQL Server can use on server.

Now, though it sound simple (which it is as well) there are two catch here. First, is that this memory limit is the amount of space SQL Server uses to stuff database pages which doesn't include work space required by internal SQL worker processes, which can cumulatively be another 500-700 MB of physical RAM. So, in case your task-manager is showing more memory being used by your SQL Server process, don't get surprised.

Second, is that it can be possible that taskmanager may show SQL Server using less amount of memory. This can be due to several reasons one of which is "locked pages" but whatever the reason may it be, one thing is clear that we can't rely only on task-manager's information to get exact memory usage figures. 

Reading the last point will obviously raise a question that "how should we check exact memory consumption" and my answer (which many of you may have guessed by now) is T-SQL. Following queries can be used to exactly calculate the same:

Only the BPool

select

    sum(multi_pages_kb 

        + virtual_memory_committed_kb

        + shared_memory_committed_kb) as [Overall used w/o BPool, Kb]

from 

    sys.dm_os_memory_clerks 

where 

    type <> 'MEMORYCLERK_SQLBUFFERPOOL'

Total

select

    sum(multi_pages_kb 

        + virtual_memory_committed_kb

        + shared_memory_committed_kb

        + awe_allocated_kb) as [Used by BPool with AWE, Kb]

from 

    sys.dm_os_memory_clerks 

where 

    type = 'MEMORYCLERK_SQLBUFFERPOOL'

Now, the Third and final option of this post is "Minimum Server Memory". Although it is what it say it is, again there is another catch in this option as well. Most of us assume that if we provide any number here, let's say, 4096, then SQL Server will reserve this much memory as soon as it starts, which unfortunately is a wrong assumption.

SQL Server only asks for the amount of memory which required and this required memory is calculated by Buffer Manager on the basis of several parameters at start of SQL Server.

Then you'll ask how this option is used. This option comes into play when SQL Server crosses the number mention in this option and it has to release back memory to OS, so, if we go by our example once SQL Server crosses 4096 mark in memory consumption it will not going to release this much amount of memory back to OS and keep it reserved for itself.

Hope you have found something new today about this very commonly used options. To read more on "AWE" and "Locked Pages" you can follow this link.