Share via

SQL Server Memory and Troubleshooting

  • Article

Introduction

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.

Note

Before I begin I would like to point out this discussion does not include memory configuration for SQL Server 2012. There has been changes in SQL Server memory configuration.

The following articles will help:

↑ Back to top

Memory vs Hard Disk

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).

What is 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.

How to see if SQL Server is 32 bit or 64 bit?

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

What is /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

What is PAE?

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

What is AWE?

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:

Few Important points:

  1. With PAE enabled on system if you enable /3GB switch you cannot take benefit of more than 16 G RAM. SO if capable system has /3GB switch it limits its memory to 16 G.
  2. /3GB switch has nothing to to with Physical memory it only makes changes to VAS. It has no dependency on AWE or PAE
  3. PAE does not have any relation to AWE because former allows increased visibility of OS towards memory and later allows SQL Server to access more RAM than allowed by its VAS.
  4. PAE does not affect neither it changes VAS of the 32 bit system.
  5. If you have a 32 bit system (enterprise system) with more than 4G of RAM. You need to enable AWE in SQL Server so that SQL Server can take advantage of memory present.
  6. If you have a 32 bit system with 2 G of RAM. There is no need to enable PAE or AWE.

There is one more concept I would like to shed light on is WOW ( Windows on windows system).

What is WOW?

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).

↑ Back to top

SQL Server memory

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.

What is BufferPool?

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.

  1. SQLCLR
  2. Extended Stored Procedures
  3. Memory allocated by linked server
  4. Large page allocation done by memory manager ( large pages are any pages >8 KB)
  5. COM objects

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

How to manage memory to leave aka reserve memory in SQL Server 64 bit?

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
**
**

Troubleshooting memory and Understanding DBCC MEMORYSTATUS output

Memory pressure can be

  1. External memory pressure
  2. Internal memory pressure

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

Does my system have low memory?

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.

What is Locked pages in Memory (LPIM) concept?

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

 

TIP

If your SQL Server service account is running under local system account you don't need to provide it LPIM privilege.

This article(http://msdn.Microsoft.com/en-us/library/ms190730.aspx) will guide you how to provide LPIM privilege

 

How to test that your SQL Server is facing memory crunch?

  1. Errorlog: Refer to SQL Server error log see if you can find Out Of memory error(OOM). Let me remind you getting OOM error does not always points to fact that your SQL Server needs more memory. It can point to fact that query was poorly written, SQL Server configuration(max server memory setting) is not proper, there is actually memory crunch on OS due to which SQL Server is suffering. If OS faces memory pressure it can ask SQL Server to trim it memory consumption and SQL works in non preemptive mode will start doing it.   This can happen if you have not given enough memory for OS to work so it is very important to configure MAX server setting for SQL Server instance leaving enough memory for OS to work smoothly. Sometimes some drives leak memory or some DLL's which are loaded leak memory which are installed on OS if such is the case we need to find it and get it removed.  
  2. Perfmon: This is best place to see for how SQL Server is behaving with current memory configuration.  

Following counters one should look

  1. SQLServer:Buffer Manager--Buffer Cache hit ratio(BCHR): IIf your BCHR is high 90 to 100 Then it points to fact that You don't have memory pressure. Keep in mind that suppose somebody runs a query which request large amount of pages in that case momentarily BCHR might come down to 60 or 70 may be less but that does not means it is a memory pressure it means your query requires large memory and will take it. After that query completes you will see BCHR risiing again
  2. SQLServer:Buffer Manager--Page Life Expectancy(PLE): PLE shows for how long page remain in buffer pool. The longer it stays the better it is. Its common misconception to take 300 as a baseline for PLE.   But it is not,I read it from Jonathan Kehayias book( troubleshooting SQL Server) that this value was baseline when SQL Server was of 2000 version and max RAM one could see was from 4-6 G. Now with 200G or RAM coming into picture this value is not correct. He also gave the formula( tentative) how to calculate it. Take the base counter value of 300 presented by most resources, and then determine a multiple of this value based on the configured buffer cache size, which is the 'max server memory' sp_ configure option in SQL Server, divided by 4 GB.   So, for a server with 32 GB allocated to the buffer pool, the PLE value should be at least (32/4)*300 = 2400. So far this has done good to me so I would recommend you to use it.  
  3. SQLServer:Buffer Manager--CheckpointPages/sec: Checkpoint pages /sec counter is important to know about memory pressure because if buffer cache is low then lots of new pages needs to be brought into and flushed out from buffer pool,  due to load checkpoint's work will increase and will start flushing out dirty pages very frequently. If this counter is high then your SQL Server buffer pool is not able to cope up with requests coming and we need to increase it by increasing buffer pool memory or by increasing physical RAM and then making adequate changes in Buffer pool size. Technically this value should be low if you are looking at line graph in perfmon this value should always touch base for stable system.  
  4. SQLServer:Buffer Manager--Freepages: This value should not be less you always want to see high value for it.  
  5. SQLServer:Memory Manager--Memory Grants Pending: If you see memory grants pending in buffer pool your server is facing SQL Server memory crunch and increasing memory would be a good idea. For memory grants please read this article: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx
  6. SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.
  7. SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.

Few Points

  1.  If Target server memory is greater than Total server memory there can be memory pressure. Let me put emphasis on word can be ,it is not a sure shot signal.Please refer to this MSDN forum thread where OP had target server memory greater than total server memory but because there were no memory grants pending ,and page life expectancy was high so there was no memory pressure . http://social.msdn.Microsoft.com/Forums/sqlserver/en-US/1503bbd9-d03e-44ab-8bc1-5d319a261a84/does-this-a-sign-for-memory-pressuer?forum=sqldatabaseengine   

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

I saw task manager, SQL Server is using very low 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

I am facing Out of memory error.

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

  1. Make sure you have set OPTIMUM value for MAX SERVER MEMORY SETTING. This is one which should be adopted as best practice.
  2.  Try to find out whether it is internal memory pressure or external. By that I mean if SQL Server is facing memory crunch it will be internal if there is less physical RAM on system it will be external.
  3.  Try to find if it is SQL Server VAS pressure. Generally in error message if you get like 'Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE ' this points to fact that OOM error came because there was VAS pressure. This type of error use to come in 32 bit system which had VAS limitation. Use below query to find out how much memory is consumed by VAS for noon buffer pool.
select SUM(virtual_memory_reserved_kb)/1024 as VAS_In_MB from/code> 
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:

  1. Singlepage Allocator(SPA)
  2. Multipage Allocator(MPA)

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.

↑ Back to top

Summary

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

↑ Back to top

Suggested Readings

See Also

SQL Server General & Database Engine Resources on the TechNet Wiki
List of articles by Shanky

This article participated in Technet Guru competition January 2014 and won Silver Mdeal

↑ Back to top