One of the things that I frequently come across when reviewing SQL Server installations is just how many of them have not been set up with appropriate memory configuration settings, or, as in many cases, not set up in the way the administrators of the system had assumed they were; usually the DBAs thought the system was set up to use all e.g. 8GB of RAM, but no changes had been made to the OS or SQL Server configuration, so their (32-bit) SQL Server would only be accessing 2 GB, and reporting that it was using 1.6 GB.
The problem is due in part to the fact that on 32-bit systems configuration changes usually have to be made both in SQL Server and at the OS level, and in part to the sprawl of documentation available on configuring SQL Server’s memory settings, as opposed to a single jumping off point which runs through all the settings and considerations that need to be made.
Add to that the black art of establishing exactly how much memory SQL Server is using (most of the obvious options will only show how much memory the buffer pool is using) and it’s easy to see why it’s such a problem area.
In this post I’ll attempt to clear some of the smog and provide what I hope will be one document which answers most of the questions that arise about configuring SQL Server’s memory usage.
This discussion will cover configuring memory for SQL Server 2000, SQL Server 2005 and SQL Server 2008 (with the exception of the Resource Governor). This blog assumes an edition of SQL Server that is not internally limited in its memory usage.
32-bit or 64-bit SQL Server?
There’s a big difference between the memory configuration settings between 64-bit SQL Server and 32-bit SQL Server, so it’s not possible to start a discussion about SQL Server’s memory management without clarifying whether we are dealing with 32-bit versions or 64-bit versions of the product, as this is key to how much memory SQL Server can address, and (almost as importantly) how it addresses that memory.
Until fairly recently 32-bit software was ubiquitous. The server Windows operating systems were 32-bit, your desktop, usually Windows XP was 32-bit. Therefore, I’ll be focusing a fair bit on 32-bit SQL Server as this is what requires the most configuration, and also where most of the confusion lies.
So, here goes.
Windows memory architecture
A quick aside on Windows memory architecture first.
The amount of memory a 32-bit process can access is 2^32 or 4294967296 bytes, or 4 GB.
On a 32-bit Windows OS this 4 GB of memory is not all addressable by a single process. Instead, it’s partitioned by the OS into two address spaces. 2 GB is kept by the OS (commonly referred to as the kernel) and the remaining 2 GB is the user mode address space, or the area each application (process) will have access to. Whilst each user mode process gets 2 GB as its addressable memory range, the kernel mode area is shared between all processes. SQL Server runs in the user mode address space and is bound by default to the 2 GB of memory limit on a 32-bit OS.
This directly addressable memory will hereon be referred to by what it is more commonly known as, the virtual address space or VAS.
Configuring SQL Server’s memory settings
SQL Server’s default out-of-the-box memory limit is deliberately set to a very high value of 2147483647 MB, which basically means all available memory, but as you should now know, there’s no way it can actually use anywhere near that much memory, particularly on a 32-bit platform.
64-bit operating systems have a far far bigger address space open to them; 8 TB to be exact. Before you run off to your calculator to evaluate 2^64, the answer won’t be 8TB, but 8TB is what each user mode application gets due to current hardware and OS limitations. The kernel also gets 8TB and this kernel address space is shared by all processes, just as in 32-bit Windows.
Having said all that, I should point out that no current MS Windows OS can address more than 2 TB.
What this means for 64-bit SQL Server is that out of the box, it can address all the memory on a server without any special configuration changes either within SQL Server or at the OS level. The only thing you need to look at is a cap on its memory usage; capping memory usage is covered in the ‘max server memory’ and ‘min server memory’ section which is further down.
To /3GB or not to /3GB
So, as 32-bit applications are natively restricted to a 2 GB VAS, OS configuration tweaks are required to allow access to more than 2 GB, and these are covered next.
The first modification is one that, rather ironically, should be used as a last resort. Ideally, it should be used on the advice of Microsoft Support (PSS).
I’m choosing to get it out of the way now because the /3GB setting is probably the most well known and most misused.
/3GB allows a 32-bit process to increase its VAS to 3 GB by taking away 1 GB of address space from the kernel, and this is why it’s a last resort; there’s no such thing as a free lunch as the removal of 1 GB of addressable memory from the OS can introduce instability. More on that shortly.
To allow a 32-bit process to gain a 3 GB VAS you have to add the /3GB switch to the Windows boot.ini file.
As I stated, this can introduce system instability by starving the OS of System Page Table Entries (PTEs). A discussion about PTEs is out of the scope of this blog, but its effects can be dramatic and cause blue-screens. The good news is that this mainly affected Windows 2000 so you should be fine if you’re on a later Windows version.
If you’re still looking after a legacy system, there is some scope for manoeuvre here, by adding the /USERVA switch to the boot.ini it is possible to reduce the VAS increase from a straight 3 GB to a lower user-defined amount which will give the OS room to breathe, and thus resolve any instability issues.
The main reason you will be advised by PSS to use /3GB is if you are suffering VAS starvation issues, such as a bloated procedure cache as it can only reside in VAS memory (also see the next section on MemToLeave) because 32-bit version of SQL Server only allow database pages to reside in the part of the SQL Server memory cache (called the buffer pool) that is utilising awe enabled memory.
MemToLeave
(EDIT:[20110630] The correct terminology for this is Virtual Address Space Reservation.)
Because of the inherent address space limitations of a 32-bit process, a certain amount of memory has to be set aside by SQL Server on startup that SQL Server uses for overheads. This memory is set aside in case it all gets used by the buffer pool.
COM objects, extended stored procs, third party backup solutions, some anti-virus apps, memory allocations exceeding 8K and the memory allocated to the threads SQL Server creates to service e.g. user connections come from a section of memory within the VAS but outside the buffer pool which is typically referred to as the MemToLeave area. This is 384 MB by default on an e.g. 2-proc 32-bit SQL. If you want to know more about how it is calculated, check Jonathan Kehayias’s post covering this.
0.5 MB is the default thread stack size for a thread on 32-bit Windows. 64-bit Windows has a default stack size of 2 MB or 4 MB depending on which 64-bit flavour of Windows you are running (AMD x64 or IA64).
SQL Server 2005 and beyond uses a formula to calculate the max worker threads setting which affects the size of the MemToLeave area.
There is a SQL Server startup parameter (-g) which can be used to increase the MemToLeave area, but again, only do this if advised by PSS (it’s ignored on 64-bit as MemToLeave or VAS reservation won’t be an issue on that architecture) as this will reduce the maximum amount of memory the buffer pool can therefore use.
4 GB of RAM and beyond
So, we know 32-bit SQL Server can use 2 GB out of the box, and up to 3 GB (with an OS tweak that is best avoided, if at all possible).
However, 32-bit SQL Server can benefit from much more memory than 3 GB with the help of OS and SQL Server configuration modifications which will be covered next.
/PAE
To address more than 4 GB of RAM on 32-bit Windows, the OS needs to have the /PAE switch added to the boot.ini file, although if your system supports hot-swappable memory you won’t need to add this as Windows should automatically be able to see the additional memory. If you’re not sure, take a look at how much memory the OS can see via System properties; if you have more than 4 GB installed and the OS is only showing 4 GB, review your boot.ini settings. I’m not mentioning specific Windows versions because the /PAE switch applies to all current 32-bit versions of Windows.
(EDIT:[20110630] For Windows Server 2008 you have to run ‘BCDEDIT /SET PAE FORCEENABLE’ from a CMD prompt running under administrator privileges).
Both /PAE and /3GB
Some systems have both /3GB and /PAE enabled.
This is fine as long as the system does not have more than 16 GB of RAM. Add any more memory and Windows will not recognise it because of the overhead required to manage the additional memory.
Clusters
No special configuration settings regarding memory settings are required for a cluster, but I thought I better mention clusters specifically because you won’t believe how many installations there are out there where there are different settings on different nodes within the same cluster.
So, make sure any OS setting changes like /3GB or /PAE are consistently applied across all nodes.
Enable AWE
After configuring the OS, you’ll need to configure SQL Server by enabling AWE (Address Windowing Extensions). AWE is in essence a technique for ‘paging’ in sections of memory beyond the default addressable range.
AWE can be enabled in SQL Server using Query Analyzer/SQL Server Management Studio (SSMS) via the following statements:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
AWE enablement is not a dynamic option and will require a SQL Server restart, so before you do that make sure the SQL Server service account has the ‘Lock Pages in Memory’ privilege assigned to it.
Once AWE has been enabled within SQL Server and the ‘Lock Pages in Memory’ privilege has been assigned you should be good to go after a restart.
(Edit:SQL 2008 R2 is the last version of to support AWE functionality. As of SQL Server 2012 the awe_enabled option is no longer supported. If you have a 32-bit install of SQL Server 2012 I strongly urge you to read that article and it’s implications on systems with more than 4 GB of RAM).
‘max server memory’ and ‘min server memory’
The more memory you give SQL Server, the greater the need to set an upper limit on how much it uses. When you start SQL Server it’ll ramp up its memory usage until it has used up all the memory it can access, which will either be an internal OS limit or a SQL Server configured limit.
A 32-bit SQL Server instance will therefore grab up to 2 GB if the workload demands it and it is on default settings.
An awe enabled SQL Server instance will go on using up all the memory on the system if the workload is there and an upper limit on its memory usage is not set.
Having said all this, the key thing to remember is that the ‘max server memory’ setting only controls the memory assigned to the buffer pool which is the memory cache SQL Server uses; every database page that the system reads or modifies is read into the buffer pool first. What this means is that there is a substantial area of memory which we cannot directly monitor or control (make sure you read the memtoleave section) but we have to ensure enough memory is available for it. It is from this area of memory that all allocations for e.g. COM objects, CLR functions, linked server queries, OLEDB providers and multi-page allocations (i.e. anything over 8K) are made from. This is what complicates sizing SQL Server’s memory usage correctly as every system will use the memtoleave area differently.
Setting a limit has the double-benefit of not starving the OS of resources and avoiding ‘Out of memory’ errors which can occur on SQL Server systems that may have a lot of memory. The latter (rather contradictory) situation can arise because SQL Server will try and allocate more memory when it is already at the system limit (if no upper limit has been set via the ‘max server memory’ setting) instead of freeing up memory it is already using.
Configuring memory for multiple instances
A third reason to set an upper limit is if you have more than one SQL Server instance installed on a single host, as this will stop the instances competing for memory.
Allocate a high enough ‘max server memory’ limit to each instance to allow it to do its job without running into memory starvation issues, whilst reserving the bulk of the memory for higher priority instances (if any) and the OS.
This is where benchmarking comes in handy.
To set a max server memory limit of 12 GB via Query Analyzer/SSMS:
sp_configure 'max server memory', 12288
RECONFIGURE
GO
SQL Server ramps up its memory usage because by default it is set to use no memory on startup. This is controlled by the ‘min server memory’ setting. Specifying this to a higher value has the benefit of reserving a set amount of memory for SQL Server from the off which can provide a slight performance benefit, especially on busy systems. It’s actually not uncommon to see ‘min server memory’ and ‘max server memory’ set to the same value, to reserve all of SQL Server’s memory straight away. The downside is SQL Server will take slightly longer to start up than if ‘min server memory’ was set to a low value.
Older SQL versions did not really release memory
This will probably get me into a bit of trouble, as there are KBs that clearly state that SQL Server releases memory when the OS is under pressure.
True, but only under a lot of pressure and SQL Server 2000 and it’s forerunners were notoriously bad at this – often requiring a sytem restart which is why it vital to set an upper limit via the ‘max server memory’ for any system (not just SQL Server 2000).
Memory corruption
Slightly off-topic, but this is an appropriate place to bring this up.
Certain builds of Windows 2000 and Windows Server 2003 contained a potentially serious memory corruption problem which affected SQL Server more than other applications, mainly because there are few applications that run on Windows that can utilise the amount of memory SQL Server does.
It’s difficult to overstate the problems this can cause, so make sure you’re on the appropriate Windows Service Packs if you’re running SQL Server on a PAE enabled system.
Another issue that arose in SQL Server 2000 SP4 was a bug that meant SQL Server only saw half the memory on awe enabled systems, although it was identified quickly and the hotfix for this was placed alongside the SP4 download.
32-bit SQL Server on 64-bit Windows
If you have a 32-bit SQL Server on 64-bit Windows the SQL Server process can access the entire 4 GB VAS.
Checking SQL Server’s memory usage
This is another area where there is lot of confusion, so below is a run-through of the most common methods for confirming SQL Server’s memory usage.
Ignore Task Manager
If you have an awe enabled SQL Server instance, do not rely on Task Manager to display memory usage as it does not show the AWE memory a process is using, so the memory usage figure it presents for the SQL Server process (sqlservr.exe) will be incorrect.
DBCC MEMORYSTATUS
Running the above command outputs the memory usage of SQL Server including how that memory is allocated, so unless you need to know how and where that memory is being used, the output it generates can be a bit bewildering. The important bits of this output pertaining to SQL Server’s total memory usage are as follows:
Buffer Counts Buffers
------------------------------ --------------------
Committed 3872
Target 65536
Hashed 2485
Stolen Potential 60972
External Reservation 0
Min Free 64
Visible 65536
Available Paging File 702099
The key figures in the above output are committed, target and hashed.
Committed is the amount of memory in use by the buffer pool and includes AWE pages.
Target is how big SQL Server wants the buffer to grow, so you can infer from this whether SQL Server wants more memory or is releasing memory.
There’s an excellent KB on interpreting all the output INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage for SQL Server 2000 and How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005.
Edit (05/02/09): Remember the buffer count numbers refer to pages of memory which are 8K in SQL Server
System Monitor (perfmon)
Perfect way to get a quick reference on exactly how much memory SQL Server is using at that moment. Start System Monitor and add the SQL Server: Memory Manager: Total Server Memory (KB) counter.
Replace “SQL Server” with MSSQL$ and the name of the named instance if it’s not a default instance, e.g. MSSQL$INSTANCE1.
‘Total’ memory usage
When trying to establish exactly how much memory SQL Server is using it’s not just the buffer pool memory you have look at, but the MemToLeave area as well. The key point to bear in mind here is that it’s not only SQL Server that can make allocations from this latter area of memory but third party processes as well, which can make it impossible to precisely account for SQL Server’s absolute memory usage, contrary to some myths out there about calculating SQL Server’s memory usage via e.g. DBCC MEMORYSTATUS, as such methods can only account for SQL Server’s own memory allocations and not allocations by foreign processes.
Edit [20110627]: Soft NUMA section removed.
64-bit
I mentioned at the start of this post that all you have to worry about for 64-bit SQL Server is setting a max memory limit as SQL Server can access all the memory current Windows operating systems can support, and 8 TB in total. That’s mostly true, with the exception of a certain privilege that the SQL Server service account needs, and that’s the ‘Lock Pages in Memory’ privilege.
This privilege is vital as it prevents the OS from paging out SQL Server memory to the swap file.
With the introduction of SQL Server 2005, this right was restricted on 64-bit Windows to only take effect on Enterprise Editions of SQL Server, so if you’re wondering why your huge new multi-gigabyte multi-core 64-bit system is paging like crazy, this might be why. [Edit: This has finally been reversed for both SQL Server 2005 and SQL Server 2008 Standard Editions: http://blogs.msdn.com/psssql/archive/2009/04/24/sql-server-locked-pages-and-standard-sku.aspx
Whilst we’re on the subject of paging on 64-bit SQL Server systems, take a look at the following KB:
How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005 which covers issues a number of issues that cause SQL Server’s (Standard or Enterprise editions) memory to be paged out.
If your 64-bit system has hundreds of GB of RAM, don’t assign it all via the ‘max server memory’ setting. The advice if you have a lot of memory changes with each version of SQL Server. In SQL Server 2005 the (very) general recommendation was to leave aside 1 GB for every 16 GB, so if you had a 256 GB system this would mean setting ’max server memory’ no higher than 240 GB.
For SQL Server 2008 the SQLCAT team posted a best practices article (based on an OLTP system) advising 1 GB to be set aside for every 4 cores, so if our 256 GB system had 32 cores that would be a ‘max server memory’ setting of 248 GB. The usual caveats of assuming this is a system dedicated to SQL Server apply.
Personally, I’d be more cautious and use the former strategy and then baseline the system for a few weeks taking into account the System Monitor (perfmon) counter: Memory > Avalailable MBytes as this will show how much free memory the system has and I’d tweak the max memory setting accordingly, as every system will use CLR functions, linked server queries, 3rd party DLLs differently (i.e. all the stuff that runs in the db but does not use memory from the buffer pool. The buffer pool is the only facet of SQL Server’s memory usage that the ‘max server memory’ setting controls).
In summary…
The table below describes how much memory SQL Server can use, and assumes an edition of SQL Server that has no internal limitations as to how much memory it can use, e.g. Express and Workgroup editions are limited to 1 GB and 3GB respectively.
| SQL Server type | Installed physical memory | ||
| Up to 4GB | More than 4GB (/PAE enabled 1) | ||
| 32-bit SQL Server | Default memory usage | With /3GB 2 | All available RAM3 |
| 2 GB | 3 GB | ||
| 64-bit SQL Server | All available RAM 3 | ||
1 Not all 32-bit systems now need to have /PAE explicitly set in boot.ini for the OS to see more than 4 GB of RAM 2. Assuming /USERVA switch has not been used to tune memory usage to between 2 GB and 3 GB 3. Assuming 'max server memory' is left on defaults, otherwise SQL Server will use no more memory than that stipulated by the 'max server memory'setting. |
|||
When I started this post I wanted to keep it as short and succinct as possible, but I realised pretty quick that that as never gonna happen, as there’s a lot more to configuring SQL Server’s memory usage than simply setting a ‘max server memory’ limit.
It’s a complex undertaking, especially in a 32-bit environment. It’s not easy to cover all the pertinent points without branching off and describing the different areas of its memory architecture, although I’ve tried to provide the relevant information without going into too much detail.
The key thing to remember is that the ‘max server memory’ setting is a misnomer and only accounts for the memory assigned to the buffer pool. Memory assigned to threads, linked server queries, the CLR and a host of other processes utilise memory from outside the buffer pool.
Hopefully, this post has helped clarify a little of how to configure SQL Server’s memory usage and provided enough information to answer most memory configuration related questions, although, as you might have guessed, there’s no black-and-white way of precisely determining SQL Server’s memory usage as there are so many external processes that can make allocations from within SQL Server’s address space…
Thanks also to Jonathan Keheyias for additional information and comments on this post. Please read his post on this topic if you want to delve a bit deeper.
Useful links
How It Works: DBCC MemoryStatus Locked Pages Allocated and SinglePageAllocator Values
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
Is there any advantage to enabling AWE on a SQL2005 Windows 2003 server with 4GB of memory ? The books online seems to indicate that it is recommended to use AWE even under 4GB and that the AWE memory allocation is dynamic so SQL will release AWE memory if under memory pressure.
You can enable awe, but it won’t have any effect as 32-bit procs can only ‘see’ 2 GB of memory as the 4 GB you have will be split between the OS and applications.
If you can’t add more memory you can test the /3GB switch which should let SQL access up to @ 2.7 GB.
Not sure why BOL would imply otherwise, and regarding releasing AWE memory, the ‘lock pages in memory’ privilege is recommended for SQL Server to stop this scenario, otherwise the performance overhead would negate having the extra memory in the first place. ‘Lock pages…’ doesn’t put a total block on AWE pages being released back to the OS, but does encourage the OS to reclaim pages from elsewhere first.
We have a 32 bit windows 2003 server w/ 4GB RAM and we have enabled the /3GB switch in the boot.ini but for some reason, SQL server 2005 is only allowing ‘max server memory’ of 2GB. Do I need to change this setting manually? Our SQL server 2005 is standard version.
Assuming that the ‘max server memory’ setting is on defaults then that’s a bit strange. Can you post the entire contents of the boot.ini file and confirm the Total Server Memory perfmon setting?
Thanks for the post Ajmer,
We are running SQL2K on a Win Server 2k3 enterprise platform (PAE enabled by default, 6 GB mem. available for the OS): would AWE have a positive impact on SQL at backup time (heavy data load on the server)?
Also, sp_configure shows me that the maximum value for ‘max server memory’ is 2147483647, is this value to high reason for which I am having the server stalling at bckp time (I/O error 1450)?
Thanks in advance!
correction: in the above message, instead of “maximum value” please read “config_value and run_value”!
2147483647 is the default value which means all available memory, so I’d recommend setting that to @ 4096 on a 6 GB box. However, if it’s a 32-bit box it’ll only be using @ 1.6GB unless ‘awe enabled’ is set to 1.
I don’t think enabling awe this would have much impact on backup time, the performance of the IO subsytem you’re backing up to would have a much greater impact.
Look at striping backups and it might also be worth reviewing what BOL says about the BLOCKSIZE parameter (of the BACKUP DATABASE command) to see if experimenting with it would help you.
Hi Ajmer,
Great post, but my understanding is that as 32 bit Windows 2003 Server Standard is limited to 4GB total memory, the /pae flag should not be used, and the only way to increase SQL 2005 memory on this platform would be by using the /3GB switch and therefore limiting the kernal memory?
I believe that if I require more memory for a SQL instance, I would have to relocate it to another SQL server running either 32 bit Windows 2003 Server Enterprise (32 GB limit with the /pae) or onto a 64bit W2k3 Standard server.
Do you agree?
Cheers.
Yup – the constraints are down to a combination of SQL Server edition limitations and Windows version/edition limitations.
32-bit Win 2003 standard is indeed limited to 4 GB: http://msdn.microsoft.com/en-us/library/aa366778.aspx#physical_memory_limits_windows_server_2003.
Your only options are to upgrade your OS or use /3GB.
You can switch to the 64-bit equivalent of the OS, but you’d have to switch to the 64-bit version of SQL Server as well.
The /PAE switch is only valid on systems that have more than 4 GB of RAM installed and, of course, a host OS that can access more than 4 GB.
Hi Ajmer,
Thank you for the post. It was very helpful. My questions is if two instances of sqL server are installed with memory allocated to each one. One of them utilizes all the memory assigned and still needs more will it be able to use any unused memory from the second server?
Thank you
Thank you.
Multiple instances of SQL Server installed on the same host won’t really play fair with each other on many levels, not just memory (you might want to look at CPU and IO as well).
I would divide the memory up amongst the instances by specifiying a max memory limit for each one, otherwise they will compete for the memory and one instance may hog most of the memory (SQL will only release memory to the OS, not another instance), bear in mind you have to set aside memory from the OS as well.
Decide which instance needs more memory according to its usage and the priority of the dbs installed and set the limits. Use the perfmon counters to monitor what the instances are currently using.
Hope that helps.
Great post, thank you.
Hi
Thanks for the blog,
i wanted to know is there any way that SQL server process release unused memory
my problem is we have a single instance server containing a signle database to which atleast 10 users are conneted at a time
now this SQL server process start consuming memory upto 1.5GB in no time and it resonses slow back to the client machines
can you have any sugesion on this
Hi Jignesh,
As indicatd in the blog, if no memory limits are set then SQL Server can eventually use up as much memory as it possibly can, if the workload is there. On a 32-bit system (without awe enabled) this will be @ 1.6 GB.
There can be many reasons for the sluggish behaviour – how much RAM is there in total on the machine? If SQL Server is using most of what is available this may be a factor.
Otherwise narrow down what is causing the issue using http://technet.microsoft.com/en-gb/library/cc966540.aspx
Regards,
Ajmer
Hi Ajmer,
This is a great post. I just to to resolve one final lingering in our mind. We are porting(until our sql 2008 is ready) our 32 bit SQL 2000 SP3 server (production version) to
a 64 bit machine with 64 gig of ram with databases on a SAN. Based on the that there should be no PAE in the boot.ini. Correct?
AWE should be disable. Correct?
No max memory set. Correct?
SQL 2000 with use all available ram. Correct?
Thanks in Advance
Thanks. You shouldn’t have to enable PAE, but if it’s 32-bit SQL you’ll need to enable awe and because of that set a cap on memory usage, as it will use all the memory that edition can access.
Thanks. That was a big help
Great post! Thanks!
We have a Windows 2003 Ent server 64-bit with 8GB RAM.
We are running SQL 2005 Ent 32-bit.
We do not have /PAE or /3GB in the boot.ini and I will not add either of them.
I will enable AWE wirthin SQL and set max memory to 6GB.
I will enable “locked pages in memory” in gpedit.msc
Then I will reboot the server and hope for the best.
Does that sound correct?
Thanks,
T_Brothers
That looks about right to me. Monitoring the buffer cache size via the perfmon counters should validate those settings by showing memory usage eventually growing to 6GB and staying at that limit.
Very insteresting article.
I have a very interesting scenary. Running W2003(4GB memory)/SQL 2005
When I set max memory to maximun, Im getting long times queries; but If I set max memory to round 200 MB then they take 2 seconds. Any suggestion?
Please send me the dbcc memorystatus output from the configuration that results in long running queries.
Also, are these ad-hoc/dynamic queries or stored procs?
Great post! This has really clarified issues I had with Memory configuration in SQL Server. Plus great comments from other readers!
Pingback: SQL Server Enterprise Edition: Use more than 4GB of RAM on a 32 bit Machine?
Hi Ajmer,
Very useful and interesting post.
I have 32 gig of ram and i am using windows server 2003 sp2 … but sql sever8 still using 1.6 GB of ram …any idea
Memory Manager KB
VM Reserved 1623864
VM Committed 1622324
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
I’m assuming this is a 32-bit system?
“AWE Allocated” reports zero.
You need to enable awe as described in the post (if it’s 32-bit)
Also, check that the OS can actually only see 32GB as SQL only seems to be able to ‘see’ 16GB. This may be because both /3GB and /PAE are set in the boot.ini, something which is also covered in the post.
Question, we have a SQL Server 2005, Windows 2008 32-bit with 16GB of RAM experiencing the following error and since I’m not a guru on SQL servers, I’d need someone’s expertise to address this issue. The specs of the server is below also. Thank you for any info!
Error from SQL server log file
Date 2/23/2011 10:00:33 PM
Log SQL Server (Archive #1 – 2/23/2011 10:04:00 PM)
Source —multiple spID
Message
There is insufficient system memory to run this query.
SPECS
Memory settings are as follows:
Use AWE to allocate memory = unchecked
minimum server memory (mb) = 0
maximum server memory (mb)= 2147483647
Other memory options:
Index creation memory (in KB, O = dynamic memory) = 0
Minimum memory per query (in KB)= 99999999
Total paging file size for all drives: 3625 MB
You really need to enable AWE. Without it, your 32-bit SQL Server is only going to be using up to 2 GB and the remaining 14 GB is effectively unused (unless you have other applications on that server).
If you enable AWE you will need to set an upper limit to the amount of memory allocated to sql via the ‘max server memory’ setting mentioned in the post.
The ‘max server memory’ setting you apply depends on what else is running on that server. If it’s dedicated to SQL Server you can probably set a limit of 14336 (14 GB), although I’d play safe and set it to 12288 (12 GB) as that’s still over 6 times more than what it’s using under the current configuration.
We have SQL server 2008 R2 with windows server 2008 R2 enterprise & 12 GB of ram. my concern is that SQL server is using about 8 GB of ram & It’s using more ram some times.
any idea how we can reduce this huge memory utlization.
Regards,
Sanesh.
It shouldn’t go much above whatever you have defined as your ‘max server memory’ limit. The default value for this is 2147483647 which basically means unlimited which may be why it’s using more.
Hi,
I have a Win 2K3 Standard Edition OS (32-bit) with 4Gb of RAM and no /PAE or /3GB. I don’t have AWE enabled (no point really) so if I am setting maximum server memory what would you recommend?
32-bit Win2K3 Standard Edition only supports 4GB and the SQL process will only be able to use 2GB of that, so unless you actually want to reduce SQL’s memory to significantly less than 2GB then there’s not much point setting it. If you’re seeing memory pressure you can explore the /3GB option (entirely at your own risk) because on your existing setup that’s the only available option.
Hi,
Really appreciate this info. Thanks.
A question for you: I have Windows Server 2003 Enterprise Edition SP2 with 8GB of memory and SQL Server Enterprise Edition SP4 (with hotfix KB899761). I have configured the /PAE switch, turned AWE on and set the Max Memory to 6144MB. But the sqlserver process still will only go up to 1.6GB or memory.
When I add the /3GB switch then I can see it go up to 2.7GB of memory. Ideally I would like it to go up to 6GB of memory? Am I missing something?
Looks like you’ve done all the right things.
Can you post the first 20 lines of the current error log and the value of the perfmon counter Memory:Available MBytes.
Hi Ajmer,
Thanks for your reply. Your reponse led me to the solution. I didn’t have the sql service user in the “lock pages in memory” policy. Everythin AOK now.
Thanks again.
Nick
Hi, we have 32bit Win 2003 Enterprise and SQL Server 2000 on it.
Have 8G or RAM and AWE enabled. We want to install 32G of RAM.
I know that Win 2003, Enterprise supports physical memory up to 32 GB but can’t find any confirmation that SQL 2000 supports it too.
Do you know?
Thank you
If it’s the Enterprise edition of SQL Server then you’ll be able to use all 32GB as long as you’re not using the /3GB switch in boot.ini.
We have SQL Server 2005 64bit with 16GB physical memory and SQL Server configured Min-Max(0-13GB). this server has other application processes in addition to SQL, OS processes. The host gets reboot at midnight every day to clear the memory. how do we find out what is max usage of memory that SQL server used in a given day. Do we need to use perfmon parameters to find out? if so what are they? – thanks.
If you have other apps on that system then setting a max memory limit of 13GB leaves Windows and all the other processes just 3 GB to use after SQL Server grabs its share. if it’s not the default instance).
Use the perfmon counters ‘SQL Server:Memory Manager > Total Server Memory (KB)’ to track how much buffer cache memory SQL is using (replace SQL Server with MSSQL$
I’d also advise using the ‘Memory: Available Mbytes’ counter to track overall memory usage on that server to see how low it goes. If it drops into the low hundreds you should add more memory or reduce SQL Server’s max memory settings (which are already a bit high).
We have SQL 2005 SP3 32-bit on Windows Server 2008 64-bit with 32GB of RAM. Should we be using AWE? SQL looks to be only consuming 4GB of RAM. We have also had instances of errors “There is insufficient memory to run this query”. It seems that the memtoleave area (default 256MB?) is being exhausted after about 3 weeks of operation, requiring a server reboot. We do make use of linked servers a lot. Max memory is set to default. Any ideas on how to overcome this error? Should we use -g to increase the memtoleave are or should we use AWE? Should we lock pages in memory? Thanks.
Leave the -g setting for the moment.
It’s most likely showing 4GB because 32-bit processes on 64-bit Windows get a 4GB VAS.
Enable awe and assign the service account the lock pages in memory privilege and restart (you may have to restart the box for the privilege change to be picked up).
Run with that for a while and track the ‘SQL Server:Memory Manager > Total Server Memory (KB)’ perfmon counter, which should now show it growing to your max memory limit (and yes, assign it a max server memory limit as well).
Thanks Ajmer. Are you confident those changes will stop the memtoleave area being exhausted over time? That is my primary objective. Thanks.
I don’t really have enough information to confirm that, but enabling awe will narrow down the issue to a genuine memtoleave issue or just the fact that there isn’t enough memory.
Christian Bolton’s blog has a a view for monitoring your VAS at http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx but I would have expected out of memory errors instead of insufficient memory errors if this was a memtoleave issue. There are can be bugs with regard to the use of linked server queries and the use of extended procs so make sure your server is patched as well.
Great information!! I have a specific scenario I need some help on. I have a Windows Server 2008 Enterprise x86 (/PAE w/10GB ram) & SQL Server 2008 R2 Workgroup. If I understand correctly according to Microsoft’s website – the 32-bit version of Workgroup (2008 R2) is limited by the OS’s ram limit. So can I assume that I have up to 10GBs of ram available to SQL (less OS needs) & if so – should I then set the max memory limit in SQL? In all of this AWE is not enabled as I beleive that it is not an option for Workgroup. Thanks.
The reference in the documentation to “OS Maximum” seems very misleading to me.
Workgroup Ed does not support AWE so there’s no way (/3GB hacks aside) it’s going to support more than 2 GB on a 32-bit OS.
What are the perfmon counters showing you?
Thanks for the quick reply! As this is a new install and I don’t have any kind of real load on it, I don’t have any good metrics to provide. DBCC MEMORYSTATUS shows Buffer Pool (Target) = 204760 & Process/System Counts (Available Physical Memory) = 9681940480. perfmon Memory Manager: Total Server Memory (KB) counter = 67,072. Let me know what metrics you would like me to provide & I’ll be happy to get them. I’ll see if I can better determine what this combination of versions shows for a maximum memory limit. Thanks.
Restore a db onto it and reindex it. If it contains tables more than a couple of gigs in size I’d imagine that this would start pushing up your total server memory counter to above the limits.
Otherwise install the AdventureWorks (http://msftdbprodsamples.codeplex.com/) database and used that. You could give the buffer cache a jump start by setting ‘min server memory’ to e.g. 1GB just for this test.
Great post! Great info that is organized and easy to understand especially for a topic that seems to confuse me.
We have a Sql 2008 64bit instance running on Win 2008 64bit with 12gig of Ram. Besides AV and Backup software there are no other applications running on the server. This past weekend we received the following errors that subsequently caused our VM Virtual Center to fail (not running on server) due to and ODBC error connecting to the virtual center databases. I believe the virtual center was running some Update query against its databases.
Error: 701, Severity: 17, State: 123. There is insufficient system memory in resource pool ‘internal’ to run this query.
At this time there was a SSIS package running that inserts 865K records to a different archive database.
We did not have “max server memory” set in Sql Server. The defaults were being used. I have read so much on this issue and probably have thoroughly confused myself. Nonetheless I have set “max server memory” = 10gig.
I know I probably have not given you enough information but do you think setting the Max memory will leave enough RAM for other SQL processes or Windows processes to avoid any contention in the future or should I be looking at this in a totally different perspective? Your thoughts and comments would be greatly appreciated.
Thanks
10GB is a bit high for a max server memory limit on a 12GB x64 system. Remember, we’re only specifying the limit for the buffer pool here and there’s the overhead ‘memtoleave’ area which is reserved by sql server and will take a chunk out of the remaining 2GB.
Lower max server memory to 8 GB. Take a look at your perfmon counter Memory:Available MBytes to show you how low your remaining memory is getting (it’s a good idea to track this permanently) before and after you make the change. Let me know if that helps resolve the 701 errors.
P.S. Make sure also that you’re not running into the following issue (fixed in SP1) that affected FTS users: http://support.microsoft.com/kb/982854.
Hi Ajmer,
Really great work from your side.I have one question for you.
I have reporting database size of 1Tb and heavily used by more then 250 users every day.
I am in middle of mem. config of new server and i want you in put.
Database running on 64 bit windows server 2008 R2 / SQL server 2008 R2 64 bit.
I have 128Gb RAM on this system. i am planning for set 5GB min and 100GB max mem for sql server and leave 28Gb for OS.
do i need to enable any other sql server options.
i am also planing for enable AWE…
please give me you thought on this.
Enabling AWE on 64bit is not required as 64 bit processes can natively address the much higher memory ranges on offer on a 64 bit OS.
Make sure the SQL Server service account has the ‘Lock Pages in Memory’ privilege granted, after that, you should be good to go.
The 100GB upper limit is a bit conservative, but you know what else is running on that system so I guess you’re taking the other app requirements into account.
It’s worth tracking the perfmon counter Memory:Available MBytes to see how low that ever gets as over time this will show you if there is significant RAM not being used, and therefore if you have any scope for upping that max memory limit.
Thanks for your reply…
Well SQL Server service account has the ‘Lock Pages in Memory’ privilege granted and except database/OS there is nothing else running on this server.do you think any adverse effect , if i allocate max 100GB mem. for sql server???
That should be fine. If anything, you can probably increase the limit if the perfmon counter I mentioned previously shows plenty of available RAM free.
Hi Ajmer… Great Article. Many Thanks for taking the time to write it. As a previous poster has said you have made a somewhat confusing topic much easier to understand.
We currently have SQL 2005 Standard Edition SP3 running on a 4GB 32bit Win 2003 R2 Enterprise OS. There are four named instances installed, each have the max mem setting set to 887MB. After several years of reasonable performance we’re now starting to see memory issues develop (due to more & more DB’s being added). If we take the total RAM up to 8GB I presume all I need to do is:-
recalculate the max mem setting
add the /pae switche to boot .ini
Add the ‘Lock Pages in Memory’ privilege to the SQL Server service account
enable awe for each instance
Doesn’t look like there’s much mileage in adding the /3GB switch after reading your comments above and certainly don’t want to introduce instability to this server.
Many Thanks
Thanks for the feedback Mark.
You have to bear in mind that when you’re setting a memory limit for SQL Server you’re really specifying the size of the buffer pool. This does not take into account of the few hundred MB of overhead that is reserved (MemToLeave) per instance, which on a 32-bit system with 4 processors or fewer will be 384 MB.
I don’t know how many cores your box has, but it’s safe to assume that each instance will therefore be using > 1 GB of RAM. Not only is this more than your entire 4 GB of RAM, but it does not leave any memory for the OS and the other services/processes that box is running, so I’m surprised you didn’t have issues sooner (I’m guessing one or more of those instances was under-utilised for a while).
The steps you listed are fine, but I wouldn’t bother enabling awe. As you know, enabling awe will allow each 32-bit instance to access more than 2GB and on an 4 instance 8GB box you can’t really do that bearing in mind what I’ve stated above (unless you want to assign a particular instance the lion’s share of memory).
I hope that makes sense. Let me know if you have any questions.
Thanks for the further advice Ajmer. I take your point on AWE and can see why it’s not needed and therefore I presume ‘Lock Pages in Memory’ is not needed either? I have since been advised by a colleague that the /PAE switch is not needed as we are using Windows 2003 R2 Enterprise Edition SP2?
Is this right? I have since found some contradictory advice on the net suggestintg /PAE is not needed if you have Windows 2003 R2 installed!? In any case we have decided to upgrade to 10GB assigning 2GB to each instance and the rest to the OS, VAS etc
Cheers,
Mark
/PAE was needed on older systems to allow the kernel to address more than 4GB of RAM. Windows Server 2003 SP2 shouldn’t need it but there’s one very easy way of checking if you need it – install more than 4GB of RAM and see if the OS recognises it. Read the section on that /PAE link for enabling DEP on systems that don’t necessarily require the /PAE switch.
Having lock pages on even without enabling AWE isn’t going to hurt, but it’s not strictly required in your current setup. If you’re not going to enable it yet, don’t forget to do it if/when you decide to add more memory further down the line.
We did that today and sure enough the OS recognised the whole 8GB.
Thanks for your help & assistance
Ajmer, Thank you for your article and follow up answers to various questions!
I am running 32bit Windows Server 2003 Enterprise Edition SP2 with 4GB RAM & SQL Server 32bit Enterprise Edition. I am running into memory issues with SQL error log constantly reporting “appdomain is marked for unload due to memory pressure” and sometimes the SQL service auto restarts. I am planning to
- add extra 4GB of RAM,
- enable /PAE (I expect the OS to recognize the memory automatically, so this setting may not be necessary)
- I see “lock pages in memory” already enabled for the SQL server account
- enable AWE and set the Min/Max memory to 1GB/6GB respectively.
I hope this resolves the memory issue or at least stops the auto restart of the sql service.
Check the MS website for those appdomain messages – there are some explanations and fixes for those specific messages, otherwise the steps you have outlined should be fine.
Brill blog / artical.
Hi.
We have Win 2000 DataCenter Server and SQL Server 2000 sp3 on production server.
That server has 32 Gb RAM and /PAE and AWE enabled. All is ok.
When we transfer the same database on other server with:
64bit Win 2008 R2 Enterprise and SQL Server 2000 Enterprise sp3, 128 GB RAM and AWE enabled our troubles begin. Irregardless of how much memory in ‘max server memory’ is
32 or 64 or 120 GB, after some hours of sql restart we received WARNING: Failed to reserve contiguous memory of Size= 65536. Tried to change -g up to 384 without success.
Now use FREEPROCCACHE one an hour. We can not use SQL 2005 or above.
Have you any ideas?
Regards.
These errors relate to the virtual address reservation (aka memtoleave) which is why the amount of memory you have doesn’t make a difference.
As the blurb in the post mentions, COM objects, CLR components, extended stored procs etc make their allocation from this memory space so if you have any of these they could be leaking memory here as it’s got so fragmented that a 64k allocation cannot be made.
Other common culprits include third party backup programs and anti-virus applications (particularly those with so called buffer-overflow protection ‘features’), so I’d look into these things first.
Also, take a look at http://support.microsoft.com/kb/903002.
Finally, if none of this helps, use sqldumper to take a dump of the sql server process the next time this happens and send the output to MS.
I keep forgetting this is sql server 2000 so you’re not going to get a lot of help from MS although they might still analyze the dump if you have a Premier Support contract. Email me the DBCC MEMORYSTATUS output the next time you get the error along with the first 50 lines of the error log from your last restart.
Please also apply SP4 and the last cumulative update (build 2187) as there will be a number of bug fixes in there relating to the usage and handling of extended procs.
Hi, Ajmer.
Thank you for fast reply. Practically, we have the same environment on the both servers, except for OS: 32-bit in the first case and 64 in the second. So, all ext.procs are the same, the only third party is LiteSpeed, but it releases memory after job completion. Clients are the same. So go on.
Regards.
Thanks for posting the error log with the memory error. I won’t show it up as there was a lot there and I don’t want to expose your internal server names and IPs etc.
Firstly, check out http://support.microsoft.com/kb/818095 as it looks suspiciously like your problem and was actually introduced in SP3.
The dbcc memorystatus output looks flawed to me, for example “Buffer Distribution: Stolen=4294964783″ – if you work that out in 8K pages we’re looking at 32 TB of memory! I suspect this is just another bug in that old build.
Assuming the other output is correct, your buffer pool (max server memory) is set to 30GB, but your proc cache is 1.8GB and it looks like your memtoleave area looks like its barely 200MB which seems strange. On 64-bit Windows the VAS for a 32-bit proc is 4GB so I would expect the proc cache to be bigger (as this looks like VAS exhaustion due to proc cache growth). One thing that could explain this is if e.g. the server concerned only has 32GB? I know you mentioned the server has 128GB, but something is not adding up, so sending me the DBCC MEMORYSTATUS output will clarify that.
It’s also worth looking at your syscacheobjects view to see what is dominating your procedure cache.
Also,make sure your service account has lock pages in memory enabled.
Hi, Ajmer.
Thanks for your advices. As for http://support.microsoft.com/kb/818095/en-us, we will check our versions of files.
Here is our DBCC MEMORYSTATUS
—–begin——
Stolen 12935
Free 10821
Procedures 74739
Inram 0
Dirty 5996
Kept 0
I/O 0
Latched 1496
Other 3734013
Commited 3840000
Target 3840000
Hashed 3741525
InternalReservation 1747
ExternalReservation 0
Min Free 4096
TotalProcs 10355
TotalPages 74739
InUsePages 49217
Stolen 87654
OS Reserved 9912
OS Committed 9884
OS In Use 9469
General 4807
QueryPlan 76880
Optimizer 0
Utilities 6
Connection 683
Resource 3842
Locks 9388
XDES 178
SQLCache 678
Replication 2
LockBytes 2
ServerGlobal 49
Grants 0
Waiting 0
Available (Buffers) 198838
Maximum (Buffers) 198838
Optimizing 0
Waiting 0
Available 128
Maximum 128
—-end——
We have 128Gb RAM on this server and ‘max server memory’ for SQL – 30000 Mb.
As for syscacheobjects:
9062 lines at all
100 – Adhoc
8062 – Prepared
Proc – 25
Systab – 9
Trigger – 139
View – 3
Service account has lock pages in memory enabled.
Regards.
OK, dbcc memorystatus output in this build does not appear to show total available memory (that sql server can see).
What’s missing is the ‘Memory Manager’ section as per http://support.microsoft.com/kb/907877.
I’ve only got two more suggestions:
- firstly, lower max server memory to 26GB in case this is due to some internal limitation as sql 2000 is not supported on this platform
- secondly, disable sql litespeed for a day (schedule sqlagent jobs to e.g. take the transaction log and full backups) to see if this makes any difference – it looks like it takes less than 8 hours for these errors to crop up, so bypassing it for a day is a simple way to rule it in or out of the problem.
Let me know the results.
Hi, Ajmer.
We constantly control two parameters from this query:
select count(*) as total, sum(pagesused)*8 as [total size in KB] from master..syscacheobjects(nolock) where pagesused > 1
‘total’ increases up to 23000..25000 and ‘total size’ till approximately
1800000 after that mentioned error occures and both values automatically
decreases doubly. Then process repeats.
In addition, we use ‘xp_memory_size’ from litespeed that shows the largest
block of contiguous memory (IMHO). After SQL server start it is about 166 Mb
and then degrades constantly till about 3 Mb. When it falls below about 10 Mb
litespeed generates an error and after that does not work.
We disabled litespeed and use native backup during three days. First error occurs
after 3 hours and a half. If litespeed is enabled, the first error appears after 3 hours.
Obviously litespeed accelerates process but it is not the only reason.
SQL server seemingly resolves his problem oneself, but how errors will impact on
the user’s queries? We don’t know, so use job with FREEPROCCACHE one an hour.
The change of -g up to 512 does not affect any of three controled parameters and
it is unclear to me. We plan two more experiments: the first – return to the old server
to observe the same three parameters; the second – migrate on SP4 on the new one.
By the way, do you ever meet SQL 2000 64-bit, but not for Itanium?
Regards.
Only a memory dump will reveal what else is leaking memory in the memtoleave area. SP4 should help quite a lot as I know there are fixes here for sql’s own extended procs! Just make sure you apply either the awe fix (build 2040) or the last CU (build 2187).
The -g parameter is what I would have expected to help the most here, which is why I suggested lowering the max server memory setting as it’s almost as if sql server is operating within a 32GB window. It might be worth trying to see if it makes any difference, if you haven’t already tried it.
Please also make sure that there is no space between the -g parameter and the 512 value you are specifying to increase this area (sql litespeed should start reporting a value closed to 500MB if it works). Please also confirm when bypassing litespeed if the xp_memory_size extended proc is also unloaded? What I mean by that is that the following line should not appear when you restart:
2011-09-19 10:05:55.56 spid70 Using ‘xpSLS.dll’ version ’0005.00.00′ to execute extended stored procedure ‘xp_memory_size’.
64-bit sql would ordinarily be the way to go here, if you can get hold of it (not available for Itanium I believe) but until you can arrest this very rapid memory leak I’d expect similar issues there but with a longer time frame between the issues.
Finally, the build you are on was vulnerable to various security flaws such as the infamous slammer worm (fixed in build 818 http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=10904) so there could be something nasty like that messing up your memory (beware anti-virus apps as well as they can do exactly the same kind of damage we’re seing here with some of their so called protection features).
Hi, Ajmer.
Thanks for your advices.
Strange situation with -g switch. Before your notification we actually use “-g 512″ and litespeed started from ~266 Mb. Now we use “-g512″ for start sqlserver service, but no difference – litespeed started from ~266 Mb. Where is the problem?
We also try to use old Litespeed version (3) and it worked without errors in any conditions.
But other errors remained.
Regards.
It’s probably better to take this conversation offline now by directing further correspondence to ajmer@eraofdata.com.
Before you do that, check if the -g behaviour is the same on your x86 system, as I cannot actually remember which build of SQL introduced support for -g.
Please also confirm what errors remained after you reverted to Litespeed 3.
Hello and thank you for your time
I have a 2008 r2 server 64bit with sql server 2008 r2 64 bit and I have 5 instances on the server along with other applications installed. the total physical ram is 8 gigs. I noticed that it has been getting high up as 95% usage and sql was the culprit. I am looking fro some suggestions/advice. only 2 out of the 5 instances are moderately used the others are small db’s. i changed the max server memory to 4 gigs anD Iwas wondering if that was a good idea.
I ran perfmon on all instances and they were similar
here is an ideaof what it look ed like
connection memory:688
max workspace mem: 1,084, 920
optimizer mem: 1,024
sql cache:1,760
target server memory: 1,443,840.000
total server mem(kb):57, 728.000
the other params were 0
The OS should be allowed 2 GB, so really you have 6 GB to share between those 5 instances and the apps.
5 instances plus other applications means assigning 4 GB to one instance alone doesn’t leave much room for anything else, although it will get nowhere near using 4 GB if the free RAM is just not available.
The figures you’ve posted show that particular instance of SQL Server wants 1.4 GB, but it’s actually only using 56 MB because that’s presumably all that it can get hold of.
I’ve no idea what the requirements of the other applications installed are so, just for the sake of argument, let’s leave 2 GB for the other apps. This leaves 4 GB to share between your 5 instances. I’d assign a max memory limit of 1 GB to the instances with a higher utilisation, and 512MB to the other two, as there is the VAS reservation (memtoleave) area of a few hundred MB for each instance which will take up the remaining memory.
Keep track of the perfmon counter Memory: Available MBytes to show how much free RAM is permanently available to see if you have any memory left to play with.
This is a great article.
I’ve inherited a SQL Server database. I was trying to enable Database Mail and received an error about AWE requiring ‘lock pages in memory’ privilege being required. So then I ran sp_configure to determine if AWE was enabled. It shows config_value is 1 and run_value is 0. And I see in the logs the ‘lock pages in memory’ privilege error has been reporting for some time. The database server is Windows 2008 Standard 32 bit with SQL Server 2008 R2 32-bit, so we have the 4GB of ram. So I’m planning to disable the AWE option because I’m not sure why it was enabled to begin with. Is this correct? Is there anything else that I should consider? I looked at the ini file and it doesn’t appear to have been modified.
It looks like the awe enabled option has been activated but it has not taken effect because the run value is still 0 as this option requires a sql restart. An old error log might still be present and give you an idea of when it was switched on, but as you’ve already established, it’s not going to help you unless you add more than 4GB of memory.
There’s a lot of misinformation and misconceptions about when awe needs to be enabled (which is partly why I wrote this post) and I’m guessing whoever was administering that db before thought they needed it to access all 4GB, (even though you wouldn’t want to do that anyway on a system with just 4GB). Lock pages being enabled won’t hurt you, but won’t be doing anything as you really need when you enable awe.
If you’re running into memory issues you can look at enabling the equivalent of the /3GB (USERVA) switch via bcedit as described at http://blogs.technet.com/b/askperf/archive/2009/04/03/who-moved-my-3gb.aspx, but be careful and test it first.
Thanks Ajmer. I’m planning to disable the AWE option and restart SQL Server. It’s hard to say if there are memory issues because there’s no maintenance jobs running such as rebuilding or reorganizing indexes and updating the statistics. I’m planning to put those into place and determine where else needs review. The database is somewhat large and there are massive inserts each day. I found a script to check how bad the indexes are defragmented. I suspect that it will be pretty bad.
עכשיו אני שמח כי שמתי לב באתר זה, בדיוק את המידע מתאים אשר רצה!
We have a Windows Server 2003 Standard Edition SP2 with 4GB RAM and run SQL 2008 R2 with 4 databases (some outsourced programs are executed in this machine). PAE enabeld and AWE disabled. Server’s performance option setting (virtual memory) is set to 2046MB while SQL Server’s maximum server memory is set to 1GB. But in task manager, nearly 3.93GB is shown in PF usage section and totally 850MB is the memory usage for “sqlservr.exe” shown in tab page “Processes”. Therefore, would you please help to solve the following queries. Thanks a lot.
(1) Will the memory (1GB) assigned to SQL Server be released when it grows to 1GB? If not and it grows contineously, is there any problem to be happened (e.g. machine hanged, SQL Server not process anymore, ….etc)?
(2) How to reduce the number of GB in PF usage section and memory usage of “sqlservr.exe” of task manager?
After executed dbcc memorystatus,
Commited:102400
Target:131072
Database:34029
Dirty:969
Free:26317
Stolen:42054
Visible:131072
Stolen Potential:82464
Limiting Fator:17
Page Life Expectancy:1790417
1. It depends. What the 1GB limit you specified for max server memory means is that the buffer pool will not grow above 1 GB.
Please bear in mind the additional memory SQL uses for overheads (referred to as memtoleave on 32-bit) called the VAS reservation (on x64) which can account for much more memory, especially if third party utils are used within SQL Server.
Please note my comments below as you’re not getting near that 1GB limit.
2. A commited pages value of 102400 is 800MB. The target size is 131072, which is 1GB your max server memory limit.
What this implies is that there is either not enough free memory on your system to allow SQL Server to use 1GB for its buffer pool or not enough load on it to force it use 1GB for the buffer pool.
Please review the perfmon counter [Memory: Available MBytes] to show how much free RAM is permanently available to see if you have any memory left to play with. I suspect it is at (or very close to) all zero available RAM because your PF usage is so high, so you’ll either need to review the processes consuming memory on your system to see if they’re really needed and/or reduce SQL’s max memory settings to a value that the perfmon counter [Memory: Available MBytes] shows as being constantly available (plus a buffer of 100MB or so). This should resolve the excessive PF usage.
Adjusting the SQL Server max memory limits is a dynamic option, so no restart is required.
Also, PAE enabling the server is not going to help as that is only required for 32-bit systems to access more than 4GB.
Refer to previous post, I also want to know
(3) Is there a need to restart SQL Server after the maximum server memory had been changed?
Thanks Ajmer. But after viewed your comments, I have the following questiones. Please help.
(1) Before post my query, the max server memory had been changed twice (2147483647 –> 800MB –> 1024MB) within 30 minutes. Any negative impact on it?
(2) Do you mean that I need try to reduce the SQL max server memory from 1024MB to 800 MB in order to see whether commited pages value increases and PF usage drops?
(3) As per you mentioned on “processes consuming memory”, does it mean my information in previous post “850MB is the memory usage for “sqlservr.exe” shown in tab page “Processes”? If not, where can I found it?
(4) How can I get the information for “perfmon counter [Memory: Available MBytes]“? If found the figure of free RAM, how much free RAM will be a desired figure in my case?
(5) An decrease of 100MB in max server memory will lead to an increase of 100MB in “perfmon counter [Memory: Available MBytes]“?
1. Unlikely, as your system does not appear to have enough memory free to support more than 800MB.
2. Yes, if you actually have that much physical memory free (you’ll probably need to restart SQL and bear in mind that most processes will have memory reserved in the page file and that does not actually mean they are paging).
3. This is a reference to other processes running on that server that may be consuming memory.
4. Use System Monitor (aka perfmon).
5. Yes (assuming nothing else steps in to use up that memory).
Hello and thanks for your article. It is a good write up.
Please give me advice on my situation.
I am running 2008 enterprise x64 with 64GB ram. My sql server is 2005 enterprise x86.
The most ram that I can get the process to use is about 3.5GB.
Is there any modifications that can be done to force it to use more memory?
Thanks for your help, I much appreciate it.
Robert
I’ve completely rewritten my answer (and withdrawn the previous responses to try and minimise any further confusion) as I’d made an incorrect assumption about awe on x64 systems (had to blow away a few cobwebs as it’s been a long time since I visited this particular area).
Whilst enabling AWE has no affect on x64 SQL Server, your x86 version of SQL Server should work much the same as it would on a 32-bit OS so you will be able to address more memory than the 4GB (default) limit you are coming up against by enabling awe.
So, sorry for confusing the matter, but if you just enable awe and cap the memory (and keep an eye on available memory you should be good to go.
If you’re still having issues, email me the sp_configure output (with ‘show advanced options’ enabled and the DBCC MEMORYSTATUS output).
Refer to my post previously, we are using Windows Server 2003 Standard Edition SP2 with 4GB RAM and run SQL 2008 R2. I had set the sql server max server memory to 896MB on 15 Dec 2011.
The memory used for sqlservr.exe in task manager are listed below. It is increasing everyday and had exceeded the our setting of sql server max server memory already.
15 Dec 2011: 868,520KB (848MB)
22 Dec 2011: 950,148KB (927MB)
(1) Why it will exceed the limit even I had set the limit to 896MB?
(2) Will it use up all the available memory of server and lead to machine hanged? Or it will stop increasing up to a specific MB figure?
(3) Since the sql server is supporting a 7X24 system, so we cannot stop and restart sql service for reducing the memory used figure. Is there any long term solution can be provided for reducing the memory and keep it in a low figure?
Thanks for your help.
The max server memory limit defines the size of the buffer pool only.
Unfortunately, this does not and can not dictate SQL Server’s total memory usage.
The MemToLeave section of the post outlines where other processes and objects can use SQL Server’s memory, and therefore what to look out on your system to what might be contributing to the memory usage if it continues to increase.
If your memory usage increases beyond your max server memory limit added to your memtoleave (384 MB on a typical legacy x86 system) size then it’s reasonable to infer you have something leaking memory in SQL Server’s address space and take a look a those other processes mentioned that might be contributing to this. It’s also worth patching your SQL instance to make sure you’re on the latest SP level as it’s not unknown for some internal bugs to cause memory leak issues.
Hi Ajmer,
I would like to clarify my doubts related to memory.
The server is question is Windows 2003 32 bit enterprise Service Pack 2 (physical memory is: 64 GB) and has sql server 2000 SP4 (8.0.2039) 32 bit Enterprise.
The AWE and /PAE switch are enabled and the maximum server memory is set to 61440 MB.
When I look the Target server memory counter(SQl Server: Memory Manager) in perfmon, I am seeing 33287752 KB (maximum value). I was thinking the maximum value in the perfmon counter referenced here (Target server memory ) should be equivalent to maximum server memory set in server properties as Target server memory is the memory sql server can use. Is that not the case? Can
you please clarify? Thanks in advance.
Regards,
Adam
Is it because of the bug you mentioned in your post or is there any memory cap for sql 2000 on windows 2003?
Yup, you’re hitting that infamous bug. Apply the last CU for SP4 (build 2187).
Hello, very good post, thank you.
Question: how do you consider and factor in memory needs of SQL services such as SSIS, SSAS etc? These applications utilize memory outside of the SQL mem manger’s control, yes?
thank you for insight into this question.
Rick
Good question. Although SSIS and SSAS from part of the SQL Server stack they have completely different memory architectures so you would have to monitor their peak memory usage by reducing SQL Server’s max memory settings to ensure SSIS and SSAS have enough room and to see how high their memory usage goes under peak loading.
In my experience on servers where we had to run SSAS/SSIS alongside SQL Server (something we tried to avoid if they were high load systems) the SSIS/SSAS process will peak at a certain level depending on the workload thrown at them and we would adjust SQL Server’s memory settings accordingly, or just add additional memory if the peak uses of the SSIS/SSAS processes took up too many resources. I’d also highly recommend adjusting SQL Server’s max degree of parallelism settings otherwise SQL Server will saturate the CPUs denying SSIS/SSAS cpu time when any e.g. data loading/transformation is taking place. You can also look at SQL Server’s CPU affinity settings (bearing in mind SSIS/SSAS don’t yet have similar functionality).
Thank you for such a clear and concise post on this subject.
I wasn’t even aware of DBCC Memorytstatus, as it is not documented in BOL.
Regards,
Stephanie
Pingback: MS SQL Server memory configuration » { mefyi.com }
Thank You!!!!!!
You saved my weekend and hours on the phone trying to figure out why SQL Server would not use all 60GB I gave it.
Thanks,
Andrew
Thank you very much for the article.
I will admit, I am guilty of misconfiguring my 32 bit sql servers!
I found your article because i was experiencing the infamous “out of memory” errors from a client application that accesses sql server.
My sql server is a vm running windows 2008 Enterprise (with 16 GB RAM) and SQL Server 2008 Standard Edition.
I implemented the suggestions from your article (PAE switch with easyBCD and also set AWE in sql server with a max memory setting of 14GB).
The problem has disappeared.
However, i noticed something weird afterwards….
Prior to making the configuration changes from the article, taskmgr reported that sql server was using 1.6 GB of memory.
After the configuration changes, taskmgr shows that sql server is using only 200-300 MB of memory…..is this normal?
Glad it helped you resolve your issue. The Task Manager behaviour is indeed ‘normal’ as it does not track process usage of AWE pages which is why you get an incorrect number. You can use dbcc memorystatus to dump out memory usage and it will show how much AWE memory is in use.
Hi Ajmer,
I have stand alone SQL server Enterprise Edition 32bit installed on a 32bit Windows server 2003 SP2 Enterprise Edition. having 32GB of RAM
the SQL server is using 2GB only from the 32GB RAM.
I did All the required Setting , but unfortunately nothing changed
1- SQL Server service account has the ‘Lock Pages in Memory’ privilege assigned.
2- AWE is Enabled. (SQL Log file show that AWE is enabled successfully)
3- max server memory’ is set to 12GB
4- Tried to use /PAE , even though I knew that it is not required .
5- my system does see all the 32GB Ram
I tried to use /3GB to at least use 16 out of the 32GB RAM but the system did not boot successfully after the restart and a had to rollback.
My server is : HP ProLiant BL460c G6
I have 2 server and I tried the above steps on both of them but it didn’t Work.
any new Ideas ???
Can you confirm how you’re confirming that it’s just using 2GB?
Please also email (ajmer at eraofdata.com) me the DBCC INPUTBUFFER statement output.
Thanks,
Ajmer
Actually after checking the Allocated Data for AWE today at peak time on the production server I found it 3358720 KB.
I emailed you the output file of DBCC MEMORYSTATUS command and DBCC INPUTBUFFER
Yup, your AWE allocated is showing 32GB which is actually a bit high as it does not leave any RAM for the OS.
I’d recommend leaving at least 4GB free for the OS so set your max server memory to no more than 28GB (and a bit less than that if you can help it).
max server memory is already set to 12GB
from the report I sent you and from my last comment , I can see that the memory allocated for AWE is (3358720 Kb) and that means 3.2 GB not 32GB if I am not mistaken ,, and this is the problem.
when I check the physical memory of the server during the peak time I find it no more than 4.5 GB . that is the AWE allocated plus the OS reserved .
Sorry – yes you are correct – I misread the numbers.
What’s the perfmon counter Memory: Available MBytes reporting?
Also, please can you email me:
– the sp_configure (with show advanced options enabled first) output
– the first 30 lines from the top of the current server error log
– the output of xp_msver
Great blog, but I am a SQL newbie, so I need some clarification and help. I have a Windows server 2008, 64 bit with 16GBs of memory. I am running SQL 2008, 64 bit. We are running into a resource exhaustion issue on the server with sqlserver.exe taking up 2,302,627,840 bytes and then reportingservicesservice.exe coming in second using 200,101,888. What I don’t understand is why SQL is only taking 2.3 GBS when we have a total of 16 GBs available. I currently have the default values defined (2147483647 MBs), no maximums. Thanks!
Can you confirm what the perfmon counter states (SQL Server: Memory Manager: Total Server Memory (KB))?
Also, run the Buffer Counts section of the DBCC MEMORYSTATUS command (the post gives more details of how to get both details).
If I have 12GB physical RAM and running SQL 2008 on Windows Server 2008. How much is recommended as max_memory? I assume leaving about 2GB free for the OS? But we have 10 databases connected and 4 of them are rather large (15GB+). Do I need to configure large pagefile also?
8GB should be a safe limit but it depends on what other applications run on that server.
The last thing you want to do is to have SQL use the page file which is why you limit the memory usage.
Add more memory if you can.
Thanx for the reply. So I have SQL setup for now using 8GB, I’m not doing anything else on the server so I guess that’s sufficient….But do you have experiance with GEMS? It’s a mining software and the databases I have are very large databases. Isn’t 8GB a bit on the low side when 10-15 people are connected to the databases and running their queries/calculations?
Should I activate AWE also?
15GB is a small database and 10-15 connections is tiny.
The post details how to configure memory and enable awe, if required.
It’s essential on 32-bit systems.
Firstly thanks for taking the time to write a great article.
We are experiencing issues with an application dropping connections and the SQL server will not accept any connections until the SQL Service is restarted.
The machine is running SQL 2005 standard 64bit on Server 2008 R2 Standard 64bit with 8gb RAM.
In perfmon the total target for SQL Server seemes to be limited around 2,8GB of memory, as they are both 64bit platforms I would think it would be able to address all memory on the server, is this correct? When the SQL Server hits its limit this is when connection issues occur. The memory limit for the SQL Server is at its maximum.
Not sure if the fact the server is virtualised would make a difference?
Is it worth enabling AWE and locked pages for the SQL service account in an attempt for it to be able to utilise more memory or is this not a valid fix for 64bit systems?
Many Thanks
Enabling lock pages in memory is a pre-requisite and must be done.
However, you’re running standard edition and MS pulled a bit of a fast one when SQL 2005 was released and 64-bit standard editions did not support lock pages in memory.
You have to apply CU#4 for SP3 first and then add a trace flag:
http://support.microsoft.com/kb/970279/en-us
http://blogs.msdn.com/b/suhde/archive/2009/05/20/lock-pages-in-memory-now-available-for-standard-edition-of-sql-server.aspx
Thanks so much for that information and fast reply, will be applying it to the server in the next few days and will let you know how it goes.
Hi,
I have a situation where OS is 32 bit with 32 GB RAM and version is MS Windows Server 2003 Enterprise R2 with 32 bit MS SQL Server 2008 R2.
The purpose of this machine is to run OS and MS SQL Server and Let MS SQL Server make maximum use of available memoery say 30+ GB.
Please tell us in steps, what settings I must do at OS and MS SQL Server level so that MS SQL Server uses maimum about of memory. Thanks
A reply from you would be greately appreciated as I am in need of a support.
Regards,
Suresh
The steps are pretty much covered in the post. Enable AWE, lock pages in memory and set a max server memory limit which leaves enough room for the OS (30+GB is a bit high, I would be uncomfortably configuring max memory much beyond 28GB). You can monitor the free Available MB counter to see how close you’re getting to using up all your memory (again, the post has more info on that).
Great article here. We are running SQL Server 2008 on a Windows 2008 R2 X64 server with 64 GB of RAM. The overall performance of the server is always slow. Queries run in SQL takes more time to fetch the output than the normal. At any point in time 95% of the physical memory is used. CPU usage is always at 5 to 8%, which I guess is not a problem here. Around 15 users do a RDC to this server and use SQL. Max memory is set to the default value. I manage this server, so is there anyway to increase the performance of the server as a whole? Any help would be greatly appreciated. Thanks.
15 RDC connections to the server hosting SQL Server really isn’t healthy.
Is there a reason the users cannot run their queries from their desktop?
Does SQL Server have a max server memory limit defined?
Is the lock pages in memory privilege granted to the SQL Server service account?
Thank you for your reply Ajmer. Users doesn’t have SQL installed in their local machines, hence relying on RDC. Completely agree on that point that these many RDCs will tamper the performance of the server. Max server memory limit is not defined in the SQL Server, its set to default, which means its taking up all the physical memory?
Is the lock pages in memory privilege granted to the SQL Server service account?
I am not sure how can I check if privilege has been granted to SQL service account, please advise.
Locked pages instructions are available at: http://msdn.microsoft.com/en-gb/library/ms190730(v=sql.105).aspx
Set max memory to ensure the OS and any other apps running on that server have room to breathe and probably not higher than 56GB.
As for the RDC – any host enduring 15 RDC connections is going to suffer. Installing SSMS on the users desktops will give your users enough connectivity to query the db, so what are they doing that requires RDC?
Thanks for the reply, the article which you shared says “Locking pages in memory is not required on 64-bit operating systems”. We are running a 64-bit OS, so should we consider this option, I am confused.
We will set the mas memory to the specified limit you mentioned and check for performance issues. We shall also install SSMS on the user desktops and check as you suggested.
We will educate the users to use RDC ONLY when required. Thank you for all the tips, however, please clarify my doubt which is given above..
Please see: http://support.microsoft.com/kb/918483