Wednesday, January 21, 2009

Memory Use in SQL Server

Memory Use in SQL Server

People often post questions in the SQL Server newsgroups about physical memory and how SQL Server uses it. This is a commonly misunderstood subject. The two main questions that users ask are along the lines of 1) "Does SQL Server have a memory leak? It consumes more and more memory until we run out of it and have to reboot." and 2) "If we have x GB of RAM, what's the best way to configure SQL Server?" These are actually simple questions but the answers are not normally obvious, hence the frequent confusion.

I'll tackle the first question first, as Lewis Carol suggested, in the guise of the White Queen in Alice in Wonderland (or was it Through the Looking Glass?), when he said start at the beginning, go on to the end, and then stop. SQL Server's caching behaviour is the reason for the substantial memory use. This is by design and is not a bug, memory leak nor incorrect configuration. Every time SQL Server needs to read a page from disk it caches the page in memory so that the slow disk operation will be unnecessary should SQL Server need that page again. As such, the memory allocated to SQL Server fills up, primarily, with recently read data pages. This section of memory is known as the buffer cache (in SQL Server 2000 Books Online) or sometimes the buffer pool (particularly in SQL Server 2005 Books Online).

The other main section of memory that SQL Server uses is the procedure cache. This contains all the query execution plans that SQL Server creates in order to manipulate the data, as requested by the user, in the most efficient way possible. Actually, that is not entire accurate, but it is close. When compiling execution plans, SQL Server usually comes up with several logically equivalent plans. The most efficient plan is added to the procedure cache, paired with that query string, and the remaining plans are discarded. The next time the same query string is submitted to SQL Server, it does not need to waste valuable CPU cycles recompiling execution plans because it already knows the most efficient one (compiling execution plans is computationally expensive).

Of course, there is only a finite amount of memory in a SQL box, both physical and virtual, so SQL Server cannot continue to cache data pages and query plans indefinitely. Therefore, it has a mechanism to age query plans and data pages so that, if memory runs low, the least recently used plans and data pages are removed from memory. This mechanism ensures that there is always enough memory for other applications should they request it. The ratio of memory allocated to the buffer cache versus the procedure cache is managed internally, according to an algorithm known only to Microsoft. However, if very few different queries are executed compared to the number of data pages manipulated then the buffer cache will be considerably larger than the procedure cache (and usually is). Conversely, if a large number of different statements manipulate very little data then the procedure cache will be comparatively larger.

SQL Server uses memory for other purposes too, such as the environment associated with each user connection, locks and index creation; however, the buffer cache and procedure cache comprise the majority of memory used by SQL Server. This is why memory is so important in a high-performance SQL server.

It is time to address the second question of how to configure the SQL Server memory settings given a certain amount of physical RAM in the box. In order to do this, however, we quickly need to cover a few basics of 32-bit addressing. (These 32-bit addressing issues go away with 64-bit architecture.) Every 32-bit application, be it SQL Server or something else, has a 4GB virtual address space (2^32 = 4GB). Windows assigns half of that 4GB to user code and the other half is reserved for kernel code. Note well that this is virtual address space we are talking about, and may be backed by either physical memory or the system paging file. A given page in the buffer cache (or procedure cache for that matter) may not necessarily reside in physical RAM; it may be on disk just like the database file(s). Since Windows 2000, the operating system has provided a feature to reduce the kernel mode memory to just 1GB, thereby allowing the user mode portion 3 of the 4GB in the virtual address space. Microsoft achieves this by the system-wide boot.ini /3GB switch and it applies to all 32-bit applications running on the box (not just SQL Server). Windows 2003 has introduced a new boot.ini switch, /USERVA (see, which essentially does the same thing as /3GB but allows finer tuning of exactly how much memory is allocated to the kernel code (between 1GB and 2GB).

Now, SQL Server is written to take advantage of physical address extensions (PAE). PAE is essentially a kludge that allows 32-bit applications to address memory above the 4GB address space. (64-bit addressing allows a theoretical memory space of 16 exabytes; 16,384 petabytes; 16,777,216 terabytes; since the current 64-bit operating systems limit physical memory much more than this, it does not look like we will reach this limit in the near future; for example, Windows 2003 R2 Datacentre x64 Edition supports up to 1TB of memory.)

In order to access the memory above the 2^32 boundary, an application must use a memory extension API such as Address Windowing Extensions (AWE), as SQL Server does. AWE memory is always backed by physical memory and cannot be swapped out to disk, so every memory page that is allocated to SQL Server remains allocated to SQL Server in physical memory; SQL Server will not release that page once it has been allocated. It may reuse that memory for a different data page, but SQL Server will not release it for use by another application. This is why the account under which the SQL Server service runs must be granted the 'Lock Pages In Memory' privilege. This behaviour has changed slightly with SQL Server 2005 when running on a Windows Server 2003 operating system. SQL Server 2005 Books Online says that SQL Server 2005 can manage AWE memory dynamically. However, it also says a few sentences later that this memory is non-paged memory implying it cannot be paged out once committed, which begs the question how this memory is dynamically managed.

Another thing to note about SQL Server and AWE memory is that SQL Server uses AWE memory, ie the memory above 4GB, only for the buffer cache. This allows a huge amount of data to be cached in memory, but does not affect the size of the procedure cache or other sections of memory that SQL Server uses. With AWE, SQL Server's buffer cache can access up to 64GB of physical memory, depending on the edition of 32-bit SQL Server. AWE is not relevant with 64-bit SQL since it is not bound by the same 2^32 byte limit; however, Microsoft still recommends granting the 'Lock Pages in Memory' privilege to the service account in order to avoid excessive paging.

The important settings to note when dealing with memory, with regard to SQL Server are:
  • /3GB or /USERVA (boot.ini)
  • /PAE (boot.ini)
  • 'AWE enabled' (sp_configure)
  • 'max server memory' (sp_configure)
  • 'min server memory' (sp_configure)

/3GB and /USERVA, as already mentioned, limit the amount of the standard 4GB space that the kernel code can use to 1GB (or between 1GB and 2GB in the case of /USERVA in Windows 2003). This allows the user mode portion of applications to access up to 3GB of that first 4GB of memory.

/PAE enables the operating system to use physical address extensions thereby allowing access to physical memory in excess of 4GB.

'AWE enabled' turns on the AWE API code in SQL Server in order to access the memory above 4GB. If there is 4GB or less of physical memory in the box then SQL Server ignores the 'AWE enabled' setting.

'max server memory' defines the largest virtual address space that SQL Server is permitted to use. This is typically only used in conjunction with the 'AWE enabled' setting. SQL Server 2005 Books Online says this specifically relates to the buffer cache but SQL Server 2000 Books Online does not differentiate between the buffer cache and other memory sections with regard to the 'max server memory' setting and so the implication is that this limits overall memory use with SQL Server 2000.

'min server memory' defines the limit at which SQL Server will stop releasing memory dynamically. This setting is rarely used and usually unnecessary. It does not guarantee that SQL Server will be allocated at least this much memory. When SQL Server starts, it commits only as much memory as necessary, even if that is less than the 'min server memory' limit. As more data pages and query plans are cached, the memory that SQL Server commits increases. When this committed memory increases above the 'min server memory' limit SQL Server can free pages (down to that limit), as necessary, to keep the minimum free physical memory between 4MB and 10MB.

If 'min server memory' and 'max server memory' are configured to be equal then SQL Server does not manage memory dynamically. Memory is simply acquired gradually (as SQL Server's workload increases) up to that figure and then neither released nor further acquired. However, if 'min server memory' and/or 'max server memory' are assigned different values, such as the defaults of 0 and 2,147,483,647 respectively, then SQL Server will dynamically manage its memory allocation between those two figures. Memory allocation may grow up to the 'max server memory' limit and be reduced down to the 'min server memory' limit. This dynamic memory management is the more common scenario with SQL Server.

The easiest way to think about how these settings interact is with a matrix as follows:
RAM/3GB/PAE'awe enabled''max server memory'
0-3 Not necessary
>3-4* Not necessary
>4-12***(Physical RAM) - (RAM needed for other applications & OS)
>12 **(Physical RAM) - (RAM needed for other applications & OS)

(The RAM figures above are all GB; also, I apologise for the table formatting but it is very hard to do with this blogger template.)

These combinations are not the only ones possible but they represent the common scenarios. Did you notice that the /3GB boot.ini switch is not recommended over a certain memory size? This is because the kernel mode portion of that first 4GB of RAM is used to hold various system structures including the table that the operating system uses to map physical addresses over 4GB. By limiting the kernel mode space to 1GB you are also limiting the amount of physical memory that can be mapped over 4GB (since you are limiting the size of this memory-mapping table). Different experts define this threshold at different figures between 12GB and 16GB. To err on the side of caution I tend to use the lower figure as the boundary about which to remove the /3GB switch from boot.ini.

Well, I think that is more than enough information to answer the majority of memory related questions that new SQL Server administrators post on the SQL Server newsgroups. Perhaps I should have split it up into two separate blog entries to make it more readable... A job for another day perhaps.


At 6/7/06 22:11, Anonymous Steen Schlüter Persson (DK) said...

Hi Mike

That's a quite good description - I couldn't have done it better myself..:-).

At 18/7/06 03:45, Anonymous Zack Rogers said...

Mike -

Thanks for the well thought out and well presented information.

The only thing I will add is in configuring SQL the user needs to monitor applications that are running.

I've never seen SQL actually give back any memory. I think SQL feels if it needed the memory once it will need it again.

At 4/10/06 23:24, Anonymous Anonymous said...

We run our SQLServer and SQLAgent services as a domain user. After enabling AWE we stopped and restarted SQL Server and an error msg said "AWE Failed -- could not lock pages in memory"

For the benefit of anyone else who runs into the same problem, special privileges are required in order for a user account other than System to use AWE. By default, no user, not even Administrator, is granted the necessary privilege. The "Lock Pages in Memory" user right can be assigned by using Group Policy Editor (gpedit.msc), which hosts the Group Policy MMC snap-in.

On restart of SQL Server then, you see you the message Address Windowing Extensions enabled in the Current SQL log which of course means that all has been successful.

At 9/11/06 07:29, Anonymous Anonymous said...

Thanks for the fantastic article - I only wish I came across it sooner!

One thing we ran into which I did't see listed is that there is a bug with SQL 2000 SP4 where SQL is only able to access 50% of the physical memory when AWE is enabled. It is resolved by installing a hotfix:

At 28/11/06 02:26, Anonymous Patrick Schnelly (CZ) said...

Great article. Finaly I've found a general explannation of the mssql memory problematics.

Thank you!

At 14/7/07 09:24, Anonymous Anonymous said...

I have a question about SQL 2005 ent edition on system with 16GB of RAM. Its paging out memory every 2-3 days and microsoft's suggestion is to lock pages in memory so no paging could occur. At the same time it says that it will stay dynamic and it will release it to OS when needed.
Questions are:
Is MAX SQL Server memory setting necessary when having 'lock pages in memory' on?
What is the difference between paging and releasing to OS on demand?
What happens if I set MAX SQL server memory to 14Gb out of 16Gb and SQL server reaches 14GB. Will it start showing out of memory errors or will it start committing it's changes in the memory and reusing it for itself?

Duke N.

At 17/7/07 00:02, Blogger Mike Hodgson said...

Duke, thanks for the questions. I started a response but it was a bit too long for a comment, so I turned it into another blog entry:
Further explanation of SQL memory use

Hope this helps.

At 15/10/07 19:51, Blogger Janesb said...

Thanks for a great article, very clear and concise. Added to my bookmarks.

At 9/11/07 02:53, Anonymous Anonymous said...

When I started to use SQL 2005, I had memory problem, the memory usage will go up to 1G quickly(in one or two days) and never release, doesn't look right. Then I set /3G and AWE, it looks like it solves the problem, even under quite heavy usage, the memory the SQL consume keeps between 100M-200M, although it is so few that you can hardly believe.
From your article, it doesn't look like the settings should work in my case since the memory of my server is 4G.
I am pretty confused now, what is going on my server?

At 25/1/08 23:10, Anonymous Anonymous said...

Very good, detailed explanation of SQL memory usage.

I'm trying to tune our SQL servers to use more memory, the Standard edition can only use 2GB but I read Developer edition can use up to 4GB. Our servers have 4GB, so I have the /3GB switch and nothing else (I think that is the correct setting).

FYI I came across an odd problem. as soon as SQL starts it took 3GB of memory but in processes it showed sqlservr.exe only has 60MB - so it was reserving it (can see it on Task Manager, Commit Charge (K)). I had two instances on one server, I noticed one instance (Standard Edition) the 'min server memory' was set to 3GB, after removing that all was OK. But the other (Developer) instance was still doing the same, then I noticed it had 'AWE enabled', once I removed that it then did not reserve the memory, very strange.

At 22/2/08 14:19, Blogger Mike Hodgson said...

anonymous (from Nov 2007), sorry about not replying sooner.

In this Microsoft KB article, it explains that "Allocations that are locked in memory will no longer be visible in any one of the following locations:" and it lists Private Bytes and Working Set in perfmon and the Mem Usage column in task manger. The way to see the memory SQL Server is using is to watch the SQLServer:Memory Manager | Total Server Memory (KB) counter in perfmon.

This would explain why it seems to be working better now but you are seeing such a small working set. Check the Total Server Memory (KB) perfmon counter.


At 22/2/08 14:39, Blogger Mike Hodgson said...

anonymous (from Jan 2008), Std edition (SQL 2005) is able to use as much memory as is available to the OS. So it should be no different to Ent (or Dev) edition as far as memory config goes. It sounds like you have it configured fine to me.

It's hard for me to imagine exactly how your server is set up but I'm guessing you're seeing the fact that with 'lock pages in memory' (which I used to think was turned on automatically when you enable AWE through the GUI, but I'm getting a bit foggy in that area now as it's been so long since I looked at it) it doesn't show the committed memory in the normal places (see my previous comment immediately above this one). The place to see memory consumption is the SQL Server:Memory Manager | Total Server Memory (KB).

If you had set one instance to have a min of 3GB then it's understandable that that would cripple the other because the min server memory setting is a minimum watermark level below which SQL Server will not give up any more memory. So, in effect, one instance would have grabbed 3GB of RAM and it wouldn't give any back. You were right to clear that min server memory setting.

I can't quite follow how the other instance was configured, but I hope this helps a bit.


At 22/5/08 02:08, Blogger Derek said...

Thanks for the in-depth article!

I'm trying to figure something out about SS 2000, and maybe you have some ideas. The RAM appears to grow beyond the limits I set.

For testing, I just set it to "Use a fixed memory size" (which I take to mean: min and max are the same, like you mentioned) of 269MB. However, 6 minutes later it's at 308,732K.

In the past, I've set it to allocate dynamically with an upper bound, but it grows far beyond that bound.

Any idea what could cause this?

At 22/5/08 06:02, Blogger Derek said...

Update to my question from this morning: This was my first time to try a fixed memory size, and it has stayed under 315MB all day (usually it grows to over a GB by early afternoon), so clearly it's working, it's just not capping it exactly where I said to. So it looks like the fixed memory size was the secret (still don't know why it completely ignored my maximum setting when I used dynamic memory allocation). Thanks!

At 9/1/09 12:04, Blogger Scott Herbert said...

Hi Mike, this is a great one stop shop for memory settings! One last thing I think needs addressing though: what recommendations are there for setting the max/min sizes of the Virtual Memory/Paging File?

In my setup, it's SQL 32bit Enterprise, 32Gb RAM, PAE swtiched on, Lock pages in memory permission given to SQL services account etc. Max and Min SQL server Memory set to 30Gb.

I assume that the OS itself (and any other small programs which might, to my annoyance, have to run on the server) will possibly want some paging file at some stage.

I've got the Page file set to min 2Gb, max 4Gb, and in my load tests it doesn't seem to be increasing from the 2Gb minimum at all. Microsoft seems to advise setting the page file to be much, much larger, up to 3 times the installed RAM, but this seems to be folly to me when we've told SQL not to page out. Any thoughts would be appreciated!

1 comment:

Anonymous said...

I think that you may be interested in another application that quickly eliminates data corruption issues in database files, please take a look at corrupted file mdf mssql 2003 tool and let me know what do you think