Two things that fascinate me most about SQL Server is Memory and Transaction log. The more I try to demystify them the more complex they become. I am writing this article to bring nit bits of SQL Server memory and how to understand its various concepts. I would try to reach every aspect of memory superficially but will make sure important points are not missed out.
I have tried keeping this article in the form of Questions and Answers to make it more interesting.
The following articles will help:
↑ Back to top
Why is memory precious and always limited on every system like you see Terabyte of hard disk but you generally see 50-200 G of RAM?
Physical memory is very fast to access and cannot be increased beyond certain limit. By beyond limit I mean to say that 2 TB. Why Microsoft tries to pay any restriction on RAM does it really a fact that RAM cannot be increased indefinitely. What about 64bit system having VAS almost infinite. So of course we can use RAM which matches 2 ^64 limit but why Microsoft does not supports it. The answer is very simple they do not support it because they cannot test it beyond 2TB and I got this answer after reading blog from Mark Russinovich see below. 'The maximum 2TB limit of 64-bit Windows Server 2008 Datacenter doesn't come from any implementation or hardware limitation, but Microsoft will only support configurations they can test'. He also showed picture of system in Microsoft which had 2 TB of RAM, but rarely you can find such scenario in real world. Just though of sharing it.
http://blogs.technet.com/b/markrussinovich/archive/2008/07/21/3092070.aspx
So with RAM limited the thought must be coming what if soon all process use up the limited RAM and new processes coming won't find any RAM available for them. This led to memory virtualization, this led to concept of Virtual Address Space (aka VAS).
VAS is simple terms is amount of memory( virtual ) 'visible' to a process, a process can be SQL Server process or windows process. It theoretically depends on architecture of Operating System. 32 bit OS will have maximum range of 4 G VAS, it's calculated like a process ruining on 32 bit system can address max up to 2^32 locations ( which is equivalent to 4 G). Similarly for 64 bit max VAS will be 2^64 which is theoretically infinite. To make things feasible maximum VAS for 64 bit system is kept to 8 TB. Now VAS acts as layer of abstraction an intermediate .Instead of all request directly mapping to physical memory it first maps to VAS and then mapped to physical memory so that it can manage request for memory in more coordinated fashion than allowing process to do it ,if not it will soon cause memory crunch.Any process when created on windows will see virtual memory according to its VAS limit.
VAS in Windows OS is equally divides between Kernel/OS Process and User Process. For 32 bit system with max VAS of 4 G Kernel/system is given VAS of 2 G and application process,here SQL Server is application process when I will use word process it means SQL Server process for all intents and purposes, will be given 2G of VAS. So theoretically this means that any application process running on 32 bit SQL Server will have maximum VAS of 2 G.
Well, you can do it with properties section on My computer and running Select @@version query, but for SQL Server 2008 and above when troubleshooting memory issue I use below DMV as this single DMV gives lots of information: NOTE: This DMV will not run in SQL server 2005 .This DMV was introduced from SQL Server 2008 onwards .
select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from
sys. dm_os_process_memory
Now let's discuss the output.
Memory_usedby_Sqlserver_MB: Indicates the process working set in KB, as reported by operating system, as well as tracked allocations by using large page APIs( as per Books online (BOL)). This is memory reserved by SQL Server .Working set means collection of pages in processes VAS that has been recently referenced.Working set memory is reserved for each process when the process is created. Value shown by this column will be almost equal to memory consumption shown in Task manager if locked pages in memory privilege(LPIM) is not there for SQL Server service account you can read about LPIM in post if you go down or you can refer to TOC. This is memory SQL Server has reserved for itself ,it may not be necessarily using this whole memory reserved.
Locked_pages_used_Sqlserver_MB: This shows memory allocated by locked pages if this value is zero it points to fact that SQL Server service account does not have locked pages in memory(LPIM privilege).
Total_VAS_in_MB: This will tell whether your SQL Server is 32 bit or 64. If This value is 8 TB SQL Server is 64 bit , if it is 2 G it is 32 bit .
When process is created on Windows OS, OS allocates or assigns Virtual Address Space to this process and then Virtual Memory Manager( i will discuss about it shortly) maps this process to Physical memory. This mapping and un mapping is fast. Two processes can have same physical memory address but there VAS address might not be same at that time. What i mean to say is two process having different VAS address can be mapped to same Physical memory address and this is achieved by fast mapping and un mapping. To control this mapping and un mapping windows has a manager called as Virtual Memory Manager. Suppose a process wants to read data from or write data into memory it references address( a location in simple terms) in it VAS. And now memory manager will look for free address in Physical RAM and if it finds one it will map this VAS address to the physical memory. If not it has to wait.
Maximum VAS a process on OS( 32 bit OS) can see is 4 G . This is biggest limitation in 32 bit windows, and 32 bit SQL Server, which puts constraint on working capabilities of system. On default ( without any tweaking) 32 bit system no matter how much physical memory you add your OS will never be able to see more than 4 G of memory. This seems kind of limitation for an OS, so to overcome this 64 bit system was designed with almost unlimited VAS(Theoretically 8 TB).
To make systems running on 32 bit more efficient, concepts like AWE , PAE and /3GB Switch came into picture. I have seen newbie, and quite a lot question on forum about these, find AWE PAE and /3GB confusing. When to enable them and how it works etc. I will try to explain each of them let me start with /3GB switch
I mentioned above that in 32 bit system SQL Server process can see max VAS of 2 G. What /3Gb switch does it affects the user( SQL process) and kernel mode VAS. When it is enabled it takes 1GB of VAS from kernel mode and gives it to SQL process so now Windows process can see max VAS of 1 GB and SQL Server process can see max VAS of 3 GB( previously it can see only 2 G). Remember this might cause performance issue with your windows operating system as it might face VAS pressure. So before enabling it make sure you test out your system. To enable /3GB switch you need to make change in boot. ini file and after making the change restart for windows OS will be required.
Below link has more details about how to enable /3GB switch: http://technet.Microsoft.com/en-us/library/bb124810%28v=exchg.65%29.aspx
There is one more concept which is similar like /3GB that is /USERVA switch. With /3GB you have restrictions on how much VAS to be taken from Kernel mode( 1 GB can be taken max). USERVA switch gives you more control over VAS distribution, like if you want to give only 512 MB of VAS to SQL Server and rest 1. 5 G to remain with OS this can be done using USERVA switch. NOTE: With /3GB switch memory limitation comes when you are using windows Server 2003.When you enable /3GB switch it will reduce memory limit to 16 G.But this does not have any affect on Windows server 2008 onwards.Please refer to below link http://msdn.Microsoft.com/en-us/library/windows/hardware/ff556232(v=vs.85).aspx
PAE means physical address extension. This terminology is no where related to SQL Server but is related to windows OS. I mentioned above that 32 bit Windows in any case cannot see more than 4 G or memory but using PAE it can see more than 4 G of RAM on Capable version of windows. In more technical terms, it can address more than 4 G of memory this is achieved by system level changes that allow mapping of 32bit pointers through to an equivalent 36bit physical memory location. It just means by enabling PAE switch OS pointer level changes from 32 bit to 36 bit( I would not go into details how it is done). So now it can access up to 2^36 which is equivalent to 64 G. Practically I have not seen any 32 bit PAE enabled system with 64G of RAM. One such system I have worked on had max of 12G RAM.
Note:Windows server standard edition has memory limitation of 4 G. So there is no point in enabling PAE on this system,this is what i referred to when i said capable version of windows.
More details in the following link
AWE means Address windowing extension again to make it clear it is not SQL Server functionality but a Windows functionality which works for SQL Server. AWE ( which is Windows API) just allows SQL Server to access more memory what has been allowed to it as per its VAS on capable 32 bit windows system. Capable here means if windows server can see more than 4 G ram which also means that for AWE to work in 32 bit system PAE has to be enabled on system( In few systems it is enabled by default). AWE API does not have system wide affect it only affects user process to be precise SQL Server process. Even in SQL Server only data and index pages can take benefit of AWE cache plans execution plans cannot take benefit of this extra memory. AWE just allows a process to access memory beyond its VAS limitation.
This memory is first mapped into processes VAS and then mapped to physical memory. AWE API works as an interface.
See this link for more details:
There is one more concept I would like to shed light on is WOW ( Windows on windows system).
When SQL Server 32 bit runs on 64 bit version of windows it is called WOW. In general 32bit on 32 bit SQL Server process has access to 2 G VAS( rest 2 G is with OS) but on WOW this increases to 4G. One interesting question I got on forum was can we, on SQL Server which is on WOW mode, enable AWE. Answer is yes you can please refer to Below blog by Slava Oak.
http://blogs.msdn.com/b/slavao/archive/2006/04/12/575152.aspx
For more on AWE/PAE/3GB refer to this article:
http://blogs.msdn.com/b/chadboyd/archive/2007/03/24/pae-and-3gb-and-awe-oh-my.aspx
This was a brief description of SQL Server memory architecture (mainly I included 32 bit system as they are complex).
SQL Server uses buffer pool to efficiently manage memory requests for SQL Server processes. It is largest consumer of memory for SQL Server. There are some memory which are allocated outside buffer pool and are allocated during system start up and are referred to as Reserved memory aka memory to leave. So buffer pool and memory to leave contributes to total memory consumed by SQL Server.
A buffer is an 8 KB page in memory, the same size as a data or index page you can consider buffer as a frame which holds data and index pages when they are brought from disk to memory. SQL Server buffer manager manages the task of reading data pages into buffer pool and also writing it to disk. It is a reserved memory store for SQL Server and by default if you do not set value for it it will take as much memory as possible. So it is always recommended as a good practice to set optimum value for max server memory in sp_configure. Buffer pool only allocates memory to requests which requires less than 8 KB pages. It is easy to allocate small contiguous amount of memory than large contiguous amount( Large contiguous amount might not be free or present to allocate). For all requests which are greater than 8 KB memory is directly allocated by windows API. All your cache stores cache plans, data and index pages are stored in this buffer pool. When user requests for a row/rows , first pages are searched in Buffer pool if not I/O request is raised to get this page from disk into memory. This I/O can be costly specially on busy system so to minimize this SQL Server caches as much as data pages possible, this might seem to user as memory leak or SQL Server taking large memory but actually it increases performance and in fact this feature is by design.
Memory allocation to following is not done from Buffer pool.
I have seen many times users complaining about SQL Server taking large amount of memory and start searching buffer pool for memory allocations but they tend to forget about allocations outside buffer pool. To look at allocations outside as well as for buffer pool I mostly refer to DBCC MEMORYSTATUS output.
For more details about SQL Server architecture please see See Also section
Quite a few times I faced this question how to manage Memory to leave in 64 bit system. Finally the answer is there is nothing like Memory to leave/reserve memory in 64 bit system. Memory to leave concept is only applicable to 32 bit SQL Server. It is not applicable to 64 bit system. In fact there is no term like memory to leave in 64 bit SQL Server. Famous Bob Ward has written in his article
http://blogs.msdn.com/b/psssql/archive/2009/08/26/come-on-64bit-so-we-can-leave-the-mem.aspx
Having said that -g parameter which is used to manage MTL(memory to leave) also has no meaning in 64 bit system.
Having said above there is no need for reserve memory in 64 bit system. But still non buffer pool allocations are made and list is same as one stated above. I would lie user to also refer to this nice blog written my Jonathan Kehayias regarding Reserved memory and how to calculate it.Please refer to see also section
Memory pressure can be
External memory pressure basically is when memory pressure is faced by Windows Operating system it can be due because of system running out of page file or due to low physical RAM on the system. In both cases if memory pressure is high process can give Out of memory (OOM) error and can terminate unexpectedly.
Internal memory pressure is one we need to be aware as this is caused when process( SQL Server process) faces memory crunch. It can be due to low Buffer pool or low system VAS. Windows OS has dedicated thread to monitor memory notification and this notification is visible to all processes running on the system. If low memory notification is signaled by OS SQL Server process starts trimming its memory consumption and will continue to do so till High memory notification is seen.
More details about memory pressure can be read from below blog by Slava Oka.
http://blogs.msdn.com/b/slavao/archive/2005/02/01/364523.aspx
This is question which comes quite often on forum, how to actually check if SQL Server is facing memory crunch. This question also comes quite often because of virtue of fact that SQL Server consumes as much memory as possible and it seems to user( perhaps to one who is not aware about this) its is a kind of memory leak. But this is not. I/O operation is costly and in fact most costliest operation so almost all mainstream OS try to minimize as much as I/O possible by caching data in memory and SQL Server uses same kind of architecture. What is does is it caches as many pages in memory as possible so that if any read to page or write to page request comes its can satisfy that request by reading the page or writing in memory. This avoids I/O, perhaps on busy system many I/O's, and operation will be very fast as memory is fastest. For this reason only SQL Server seems to use memory a lot.
Locked pages in memory is a privilege given to SQL Server service account which allows SQL Server not to trim its memory excessively when SQLOS ask SQL Server to do so. Suppose on your production system where you have given less memory to OS when configuring SQL server max memory setting , someone starts taking RDP( remote desktop) connections, perhaps many RDP connections or some rouge driver starts leaking memory. In such case OS will face memory pressure and in turn inform to SQLOS and then SQLOS will will ask SQL Server to trim it consumption. Now if LPIM privilege is not there SQL Server will start trimming its memory consumption but load can be so high that SQL Server might be paged out to disk and might even terminate( unexpected shutdown). To avoid this LPIM privilege is given to SQL Server service account so that when OS faces pressure SQL Server will not be paged out to disk . But again if SQL Server cannot trim its consumption and memory pressure comes or exceeds OS processes will be paged to disk and might lead to unexpected slowness or even shutdown of OS. To avoid this it is better to give optimum value to SQL Server memory and leave enough RAM for OS and then give LPIM privilege . Article by Jonathan Kehayias( link is present in see also section) is excellent explanation why LPIM should be there for SQL Server service account. He has also discussed about scenario where LPIM is not required please see that also.
http://blogs.msdn.com/b/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx?CommentPosted=true#commentmessage
If your SQL Server service account is running under local system account you don't need to provide it LPIM privilege.
Following counters one should look
Few Points
2.Generally on stable system these 2 values are equal. 3.Free Pages counter is removed from SQL Server 2012. And also its value does not holds importance as the values for BCHR,PLE,Target server memory and Total Server memory
This is also a common misconception that Windows task manager shows correct value for memory utilization by SQL Server. Performance Monitor (PerfMon) and Task Manager do not account for memory correctly if Address Windowing Extensions (AWE) support is enabled for 64 Bit system. Actually what task manager shows you is memory consumed by working set( private byte) not total memory. To find out memory consumed by SQL Server buffer pool as well as any large pages as pointed out above can be seen through DMV sys. dm_os_process_memory. NOTE: As pointed above also sys.dm_os_process_memory DMV will not work in SQL Server 2005 and Of course 2000( DMV was introduced from 2005 onwards).To find memory consumed by SQL server 2005 below article can be referred . http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/28/t-sql-script-to-monitor-memory-usage-by-sql-server-instance.aspx
For more details read below blog
This is general error saying query/process which was running was not able to get enough memory to complete and so it failed with Out of memory (OOM )error. To resolve this
select SUM(virtual_memory_reserved_kb)/1024 as VAS_In_MB from/code> sys. dm_os_memory_clerks
sys. dm_os_memory_clerks
where type not like '%bufferpool%'
More details can be found in How to find who is using / eating up the Virtual Address Space on your SQL Server. .
For Internal memory pressure I look at output of DBCC MEMORYSTATUS command perhaps this is best query which tells me how is my memory distributed and who are the components consuming it. Its output is confusing many times so I will try to cover how to draw inference from DBCC MEMORYSTATUS output. I would not talk about each clerk in DBCC MEMORYSTATUS output for that please refer to see also .Look at the article 'How to use DBCC MEMORYSTATUS command to monitor memory usage '
Following names are present in output I will try to explain what they mean.
VM reserved: To amount of VAS SQL Server has reserved for itself.
VM committed: This one signifies that out of reserved VAS how much has been mapped to physical memory. I told in this blog before for process to execute it first references Address in its VAS and then when this is mapped to physical memory memory allocation process is complete.
AWE allocated: This is another place from where you can see if your SQL Server is using AWE. If this value is 0 AWE is not enabled.
If you see MemoryNode0,MemoryNode1,. . MemoryNode64 this points to fact that your SQL Server is installed on hardware that is NUMA aware sometimes have faced question that is my system NUMA aware so answer is SQL server 2005 onwards is NUMA aware but what matters is is your hardware configured to take advantage of it.If it is configured you will see Nodes with different ID in Memorystatus output.These node IDs correspond to the NUMA node configuration of the computer that is running SQL Server.To read more about NUMA ( non uniform memory access) refer to below link http://technet.Microsoft.com/en-us/library/ms178144(v=sql.105).aspx
Next section comes for Memory brokers like MEMORYCLERK_SQLGENERAL,MEMORYCLERK_SQLBUFFERPOOL,MEMORYCLERK_SQLCP which has two main components:
These are actually an interesting thing to look into DBCC MEMORYSTATUS command. Single page allocator signifies memory used by particular clerk in the buffer pool. Multipage allocator signifies memory used by this clerk outside buffer pool. These MPA are common cause of OOM condition. These MPA are large pages more than 8 KB and memory allocation of large pages are done outside buffer pool directly by windows API
Anything taking MPA in GB is surely an cause of problem or matter of concern . For example SQLCP signifies area in memory which stored cached plans and if lot of Ad-hoc Queries perhaps poorly written Adhoc queries are hitting database this can lead to excessive memory consumption and ultimately to OOM error.In one situation I was able to bring things under control by enabling 'Optimize for Ad hoc workload' in sp_configure.See link for more details
http://msdn.Microsoft.com/en-us/library/cc645587.aspx
Anything taking MPA in GB like if MPA for MEMORYCLERK_SQLXP is in GB it is surely an cause of problem. This clerk refers to memory utilized by extended stored procs and memory to these procedures are allocated outside from buffer pool.
If MPA for clerk MEMORYCLERK_FULLTEXT is taking memory in GB or MPA for this clerk is in GB you need to look at your fell text search feature. Restating it sometimes subsides the issue. But I would like you to read below article on how to manage Full text indexes properly
http://technet.Microsoft.com/en-us/library/ms142560.aspx
http://msdn.Microsoft.com/en-us/library/aa175787%28v=sql.80%29.aspx
If MPA for MEMORYCLERK_SQLCONNECTIONPOOL shows memory in GB you should see that connection are properly closed from application side after they are finished. This memory maintains records or user sessions that connect.
If MPA value is quite high for MEMORYCLERK_SQLCLR this can point to fact that there is problem with SQLCLR you are using. I have still not found the document from Microsoft where it has documented all its clerks and behavior. Looking at MPA and SPA we can see which clerk is consuming more memory and can proceed with troubleshooting. Not only MPA if Single page allocator (SPA) starts taking more memory then also it can be a problem.Like if single page allocator for clerk MEMORYCLERK_SQLQERESERVATIONS starts taking more memory like if you have 16 G or RAM and SPA for this clerk shows 10G that seriously this is issue.One clerk cannot consume such a huge amount of memory .So you have to act accordingly .In this case rebuilding indexes ,updating stats and even rewriting query would help. From SQL Server 2005 onwards other useful informations like 1. Page life expectancy 2. Target server memory 3. Grants pending 4. OOM count (No of times OOM error occurred) this started from 2008 These Informations can be very useful in determining status or various parameters when OOM error occurred.Of course you can see some of them from Perfmon but my emphasis is on to use DBCC MEMORYSTATUS command. One such error someone asked me to look was 'BufferPool out of memory condition LazyWriter: warning, no free buffers found' .It seemed to me like a Buffer pool pressure I checked DBCC MEMORYSTATUS and I found that stolen potential was negative .Stolen potential is amount of memory taken by SQL Server from buffer pool for miscellaneous tasks to study more about stolen potential you can refer see also (How to use DBCC MEMORYSTATUS article).Now this value being negative means buffer pool does not have any free memory so above assumption that it was buffer pool pressure is correct.If you face such error there has to be some clerk whos Single page allocator must be taking some extra ordinary memory.
I have pointed out few Clerks which I faced problem from. And tentative idea how to move about it. It is very difficult to drill down to the root cause what caused problem but by using my approach you can actually filter down which feature of SQL Server is actually causing issue.
This article is about how you can go on with troubleshooting SQL Server memory issues and understand how SQL Server memory works. It is not a complete write-up about how to resolve OOM error, as it is not possible to discuss each issue in depth here. My main motive was to let audience know about how SQL Server memory functions. There are lot of articles about troubleshooting SQL Server memory. I have given enough idea how to move when you face OOM error. I hope this article would be helpful. I would come up with new article if I find something different and interesting.
TIP: Another important place to find a huge amount of SQL Server General & Database Engine related articles is the TechNet Wiki itself. The best entry point is SQL Server General & Database Engine Resources on the TechNet Wiki
SQL Server General & Database Engine Resources on the TechNet Wiki List of articles by Shanky