Ошибка памяти на sql сервер

---

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:

• Memory configuration and sizing considerations in SQL Server 2012
  
• http://blogs.msdn.com/b/sqlosteam/archive/2012/07/12/memory-manager-configuration-changes-in-sql-server-2012.aspx

---

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

Now let’s discuss the output.

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

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

• http://msdn.Microsoft.com/en-us/library/aa366796%28VS.85%29.aspx
• http://msdn.Microsoft.com/en-us/library/windows/desktop/aa366796%28v=vs.85%29.aspx

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:

• http://msdn.Microsoft.com/en-us/library/aa366527%28VS.85%29.aspx
• http://msdn.Microsoft.com/en-us/library/ms175581%28SQL.90%29.aspx

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.

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.

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   

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

• http://technet.Microsoft.com/en-us/library/bb510747.aspx
• http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

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.
   

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.

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.

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

• https://www.simple-talk.com/sql/performance/a-performance-troubleshooting-methodology-for-sql-server/
• How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005
• Using Sys.dm_os_ring_buffer to diagnose memory issue
• SQL Server memory architecture
• Optimizing Server Performance Using Memory Configuration Options
• Fun With Locked pages AWE and PAE
• http://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture
• Understanding VAS reservations by Jonathan Kehayias
• Debate on LPIM by Joanathn Kehayias

---

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

---

SELECT @@SERVERNAME AS [Server Name]

,total_physical_memory_kb / 1024 AS [Total Physical Memory (MB)]

,available_physical_memory_kb / 1024 AS [Available Physical Memory (MB)]

,total_page_file_kb / 1024 AS [Total Page File Memory (MB)]

,available_page_file_kb / 1024 AS [Available Page File Memory (MB)]

,system_memory_state_desc AS [Available Physical Memory]

,CURRENT_TIMESTAMP AS [Current Date Time] 

FROM sys.dm_os_sys_memory

OPTION (RECOMPILE);

GO

SELECT physical_memory_in_use_kb / 1024 AS [Physical Memory In Use (MB)]

,locked_page_allocations_kb / 1024 AS [Locked Page In Memory Allocations (MB)]

,memory_utilization_percentage AS [Memory Utilization Percentage]

,available_commit_limit_kb / 1024 AS [Available Commit Limit (MB)]

,CASE WHEN process_physical_memory_low = 0 THEN ‘No Memory Pressure Detected’ ELSE ‘Memory Low’ END AS ‘Process Physical Memory’

,CASE WHEN process_virtual_memory_low = 0 THEN ‘No Memory Pressure Detected’ ELSE ‘Memory Low’ END AS ‘Process Virtual Memory’

,CURRENT_TIMESTAMP AS [Current Date Time] 

FROM sys.dm_os_process_memory

OPTION (RECOMPILE);

GO

WITH RingBuffer

AS (

SELECT CAST(dorb.record AS XML) AS xRecord

,dorb.TIMESTAMP

FROM sys.dm_os_ring_buffers AS dorb

WHERE dorb.ring_buffer_type = ‘RING_BUFFER_RESOURCE_MONITOR’

)

SELECT xr.value(‘(ResourceMonitor/Notification)[1]’, ‘varchar(75)’) AS Notification

,CASE 

WHEN xr.value(‘(ResourceMonitor/IndicatorsProcess)[1]’, ‘tinyint’) = 1

THEN ‘High Physical Memory Available’

WHEN xr.value(‘(ResourceMonitor/IndicatorsProcess)[1]’, ‘tinyint’) = 2

THEN ‘Low Physical Memory Available’

WHEN xr.value(‘(ResourceMonitor/IndicatorsProcess)[1]’, ‘tinyint’) = 4

THEN ‘Low Virtual Memory Available’

ELSE ‘Physical Memory Available’

END AS ‘Process Memory Status’

,CASE 

WHEN xr.value(‘(ResourceMonitor/IndicatorsSystem)[1]’, ‘tinyint’) = 1

THEN ‘High Physical Memory Available’

WHEN xr.value(‘(ResourceMonitor/IndicatorsSystem)[1]’, ‘tinyint’) = 2

THEN ‘Low Physical Memory Available’

WHEN xr.value(‘(ResourceMonitor/IndicatorsSystem)[1]’, ‘tinyint’) = 4

THEN ‘Low Virtual Memory Available’

ELSE ‘Physical Memory Available’

END AS ‘System-Wide Memory Status’

,DATEADD(ms, — 1 * dosi.ms_ticks — rb.TIMESTAMP, GETDATE()) AS NotificationDateTime

FROM RingBuffer AS rb

CROSS APPLY rb.xRecord.nodes(‘Record’) record(xr)

CROSS JOIN sys.dm_os_sys_info AS dosi

ORDER BY NotificationDateTime DESC;

    Вчера MS SQL Server преподнес мне сюрприз. При создании новой базы данных он выдал ошибку «There is insufficient system memory in resource pool ‘internal’ to run this query» и «умер» – сервис SQL Server не запускался, а в ERRORLOG сыпались ошибки:

Msg 701, Level 17, State 130, Server XYZ, Line 1
There is insufficient system memory in resource pool 'internal' to run this query.

    У MS SQL Server существует два параметра управляющие использованием памяти:

Параметр	Доступный объем памяти	По умолчанию	Минимально допустимое
min server memory	Минимальный	0	0
max server memory	Максимальный	2 147 483 647 МБ	128 МБ

Почему-то мой MS SQL Server 2019 решил, что у него украли всю память и выставил себе в параметр «max server memory» минимально допустимое значение – 128 мегабайт. Все «танцы с бубном» в попытке реанимировать сервер я описывать не буду, просто расскажу решение по шагам.

1. Находим в реестре строку запуска службы MS SQL Server и добавляем в нее два параметра:
   • f — разрешает только одно соединение и запускает экземпляр MS SQL Server в минимальной конфигурации;
   • mSQLCMD — разрешает только одно соединение, которое должно идентифицироваться как «sqlcmd» (что бы никто другой не занял единственный коннект).

   

2. Запускам службу MS SQL Server. Если она не запустилась, то можно дальше не читать.
3. Подключаемся к MS SQL Server используя sqlcmd и проверяем максимально доступный размер памяти у SQL Server:

   select cast(value as integer), cast(value_in_use as integer)
   from sys.configurations
   where name = 'max server memory (MB)'

4. Параметры «min server memory» и «max server memory» являются расширенными, поэтому для работы с ними значение параметра «show advanced options» устанавливаем в 1:

   sp_configure 'show advanced options', 1
   go
   reconfigure
   go

5. Меняем значение параметра «max server memory» на нужное значение (например, значение по умолчанию):

   sp_configure 'max server memory (mb)', 2147483647
   go
   reconfigure
   go

6. Меняем значение параметра «show advanced options» обратно на 0:

   sp_configure 'show advanced options', 0
   go
   reconfigure
   go

7. Из строки запуска службы MS SQL Server убираем добавленные параметры: «-f -mSQLCMD».
8. Перезапускаем службу MS SQL Server.

First step to troubleshoot SQL Server memory is to identify whether the whether the low memory condition appears to be in MemToLeave or in the BPool or because of external memory pressure.

Note: If you do not know what is BPOOL or MemToLeave. Please read SQL Server Memory architecture before troubleshooting SQL Server memory.

If you can’t find the cause by following this blog (or) if you need clarification regarding your error paste the error you get along with dbcc memorystatus output printed in SQL Server errorlog in comments session of this blog (or) In This  face book group. We will try to assist you.

MemToLeave errors:

SQL Server 2000
WARNING: Failed to reserve contiguous memory of Size= 65536.
WARNING: Clearing procedure cache to free contiguous memory.
Error: 17802 “Could not create server event thread.”
SQL Server could not spawn process_loginread thread.

SQL Server 2005/2008
                Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 122880

                Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. This is probably due to memory pressure in the MemToLeave region of memory

Buffer Pool errors:
                BPool::Map: no remappable address found.

                BufferPool out of memory condition

                LazyWriter: warning, no free buffers found.

Either BPool (or) MemToLeave errors:
                 

          Error: 17803 “Insufficient memory available..”
Buffer Distribution:  Stolen=7901 Free=0 Procedures=1 Inram=201842 Dirty=0 Kept=572…

                Error: 701, Severity: 17, State: 123.
There is insufficient system memory to run this query.
There is insufficient system memory in resource pool ‘default’ to run this query

Working set trim and page out errors (external memory pressure)  

A significant part of SQL Server process memory has been paged out. This may result in performance degradation.

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 2007640, committed (KB): 4594040, memory utilization: 43%.

If you see above error jump to A significant part of SQL Server process memory has been paged out

Section 1 (MTL error):

If the Problem is with MTL we have to determine  whether it is SQL Server or some non-SQL component that is using the most MemToLeave memory (Remember what is in MTL? section in SQL Server Memory architecture ) .

SQL Server 2000: OS Reserved and OS Committed counters in the DBCC memory status output will tell us how many pages SQL Server itself is using in MTL.

Note: Each page is 8192  bytes so Multiply OS Committed * 8192 bytes /1024 to get value in MB.

SQLServer2005/2008:  Capture sum of MultiPage Allocator for all nodes (Memory node Id = 0,1..n)from DBCC memorystatus output printed immediately after OOM errors in SQL Server errorlog.

This will tell us how many KB SQL Server itself is using in MTL.

You can also take the sum of  multi_pages_kb from sys.dm_os_memory_clerks 

select sum(multi_pages_kb)  from sys.dm_os_memory_clerks

If SQL Server itself is using majority of the memory in MemToLeave look at  MultiPage Allocator  values in DBCC MEMORYSTATUS output to determine which memory clerk is consuming the majority of the memory.

sys.dm_os_memory_clerks output will also indicate which memory clerk is consuming majority of memory in MTL. Use the below query. You can further break down using sys.dm_os_memory_objects

{

select  *  from sys.dm_os_memory_clerks order by  multi_pages_kb  desc

select b.type,a.type,* from sys.dm_os_memory_objects a,sys.dm_os_memory_clerks b
where a.page_allocator_address=b.page_allocator_address order by  b.multi_pages_kb desc
,a.max_pages_allocated_count desc

}

If SQL Server Owned memory is very less ,than determine if there are COM objects, SQL Mail, or 3rd party xprocs being used, and move them out of process if possible.

COM Objects:
COM objects can be moved out of process by taking advantage of the optional third
parameter ([context]) at each sp_OACreate call. If the int value 4 is passed as
the third parameter to sp_OACreate, SQL will attempt to instantiate that object out
of process in its own dllhost.exe process. More information on the [context]
parameter can be found in the “sp_OACreate” topic in SQL Books Online. Warning:
most COM objects will work fine when run out of process, but some will fail. We
should run a few functional tests with context=4 to make sure that their objects
can be successfully run out of process.

Linked Server OLEDB Providers:
Linked server OLEDB providers can be moved out of process by setting the
“AllowInProcess” OLEDB provider option for that provider to 0. Provider options
are stored in the registry for each SQL instance at the location below:

Default Instance: HKLMSOFTWAREMicrosoftMSSQLServerProviders
Named Instance: HKLM SOFTWAREMicrosoftMicrosoft SQL
Server<instance>Providers

If the AllowInProcess reg value for the relevant 3rd party provider doesn’t exist,
create it as a REG_DWORD value and set it to 0. Some OLEDB providers cannot be
successfully run out of process, but most can.

Extended Stored Procedures:
Extended stored procedures always run in-process; there is no direct way to execute
them out of process. However, in some cases it is possible to host the xp’s in a
separate instance of SQL and execute them in the remote instance using
server-to-server RPCs. This technique is detailed in KB 243428.

Section 2 (BPOOL error):

If the Problem is with BPOOL

Capture sum of singlePageAllocator for all nodes (Memory node Id = 0,1..n)from DBCC memorystatus output printed immediately after OOM errors in SQL Server errorlog.

This will tell us how many KB each memory clerk is using in MTL.

sys.dm_os_memory_clerks output will also indicate which memory clerk is consuming majority of memory in BPOOL (single_pages_kb). Use the below query. You can further break down using sys.dm_os_memory_objects

{

select  *  from sys.dm_os_memory_clerks order by  Single_pages_kb  desc

select b.type,a.type,* from sys.dm_os_memory_objects a,sys.dm_os_memory_clerks b
where a.page_allocator_address=b.page_allocator_address order by  b.single_pages_kb desc

}

sys.dm_os_memory_clerks can provide a complete picture of SQL Server memory status and can be drilled down using sys.dm_os_memory_objects

Note:  single_pages_kb is Bpool and  multi_pages_kb is MTL 

Other views which can help to troubleshoot SQL Server memory issues are

select * from sys.dm_os_memory_objects
select * from sys.dm_os_memory_pools
select * from sys.dm_os_memory_nodes
select * from sys.dm_os_memory_cache_entries
select * from sys.dm_os_memory_cache_hash_tables

Few queries which we use to troubleshoot SQL Server memory issues.

--Bpool statistics

select
(cast(bpool_committed as bigint) * 8192) /(1024*1024)  as bpool_committed_mb,
(cast(bpool_commit_target as bigint) * 8192) / (1024*1024) as bpool_target_mb,
(cast(bpool_visible as bigint)* 8192) / (1024*1024) as bpool_visible_mb
from sys.dm_os_sys_info
go

-- Get me physical RAM installed and size of user VAS
select physical_memory_in_bytes/(1024*1024) as phys_mem_mb,
virtual_memory_in_bytes/(1024*1024) as user_virtual_address_space_size
from sys.dm_os_sys_info
go

--System memory information

select total_physical_memory_kb/(1024) as phys_mem_mb,
available_physical_memory_kb/(1024) as avail_phys_mem_mb,
system_cache_kb/(1024) as sys_cache_mb,
(kernel_paged_pool_kb+kernel_nonpaged_pool_kb)/(1024) as kernel_pool_mb,
total_page_file_kb/(1024) as total_virtual_memory_mb,
available_page_file_kb/(1024) as available_virtual_memory_mb,
system_memory_state_desc
from sys.dm_os_sys_memory
go

-- Memory utilized by SQLSERVR process GetMemoryProcessInfo() API used for this
select physical_memory_in_use_kb/(1024) as sql_physmem_inuse_mb,
locked_page_allocations_kb/(1024) as awe_memory_mb,
total_virtual_address_space_kb/(1024) as max_vas_mb,
virtual_address_space_committed_kb/(1024) as sql_committed_mb,
memory_utilization_percentage as working_set_percentage,
virtual_address_space_available_kb/(1024) as vas_available_mb,
process_physical_memory_low as is_there_external_pressure,
process_virtual_memory_low as is_there_vas_pressure
from sys.dm_os_process_memory
go

--Reosurce monitor ringbuffer
select * from sys.dm_os_ring_buffers
where ring_buffer_type like 'RING_BUFFER_RESOURCE%'
go

--Memory in each node

select memory_node_id as node, virtual_address_space_reserved_kb/(1024) as VAS_reserved_mb,
virtual_address_space_committed_kb/(1024) as virtual_committed_mb,
locked_page_allocations_kb/(1024) as locked_pages_mb,
single_pages_kb/(1024) as single_pages_mb,
multi_pages_kb/(1024) as multi_pages_mb,
shared_memory_committed_kb/(1024) as shared_memory_mb
from sys.dm_os_memory_nodes
where memory_node_id != 64
go

--Vas summary
with vasummary(Size,reserved,free) as ( select size = vadump.size,
reserved = SUM(case(convert(int, vadump.base) ^ 0)  when 0 then 0 else 1 end),
free = SUM(case(convert(int, vadump.base) ^ 0x0) when 0 then 1 else 0 end)
from
(select CONVERT(varbinary, sum(region_size_in_bytes)) as size,
region_allocation_base_address as base
from sys.dm_os_virtual_address_dump
where region_allocation_base_address <> 0x0
group by region_allocation_base_address
UNION(
select CONVERT(varbinary, region_size_in_bytes),
region_allocation_base_address
from sys.dm_os_virtual_address_dump
where region_allocation_base_address = 0x0)
)
as vadump
group by size)
select * from vasummary
go

-- Clerks that are consuming memory
select * from sys.dm_os_memory_clerks
where (single_pages_kb > 0) or (multi_pages_kb > 0)
or (virtual_memory_committed_kb > 0)
go

-- Get me stolen pages
--
select (SUM(single_pages_kb)*1024)/8192 as total_stolen_pages
from sys.dm_os_memory_clerks
go

-- Breakdown clerks with stolen pages
select type, name, sum((single_pages_kb*1024)/8192) as stolen_pages
from sys.dm_os_memory_clerks
where single_pages_kb > 0
group by type, name
order by stolen_pages desc
go

-- Non-Bpool allocation from SQL Server clerks

select SUM(multi_pages_kb)/1024 as total_multi_pages_mb
from sys.dm_os_memory_clerks
go
-- Who are Non-Bpool consumers
--
select type, name, sum(multi_pages_kb)/1024 as multi_pages_mb
from sys.dm_os_memory_clerks
where multi_pages_kb > 0
group by type, name
order by multi_pages_mb desc
go

-- Let's now get the total consumption of virtual allocator
--
select SUM(virtual_memory_committed_kb)/1024 as total_virtual_mem_mb
from sys.dm_os_memory_clerks
go

-- Breakdown the clerks who use virtual allocator
select type, name, sum(virtual_memory_committed_kb)/1024 as virtual_mem_mb
from sys.dm_os_memory_clerks
where virtual_memory_committed_kb > 0
group by type, name
order by virtual_mem_mb desc
go

-- memory allocated by AWE allocator API'S
select SUM(awe_allocated_kb)/1024 as total_awe_allocated_mb
from sys.dm_os_memory_clerks
go

-- Who clerks consumes memory using AWE

select type, name, sum(awe_allocated_kb)/1024 as awe_allocated_mb
from sys.dm_os_memory_clerks
where awe_allocated_kb > 0
group by type, name
order by awe_allocated_mb desc
go

-- What is the total memory used by the clerks?
select (sum(multi_pages_kb)+
SUM(virtual_memory_committed_kb)+
SUM(awe_allocated_kb))/1024
from sys.dm_os_memory_clerks
go
--
-- Does this sync up with what the node thinks?
--
select SUM(virtual_address_space_committed_kb)/1024 as total_node_virtual_memory_mb,
SUM(locked_page_allocations_kb)/1024 as total_awe_memory_mb,
SUM(single_pages_kb)/1024 as total_single_pages_mb,
SUM(multi_pages_kb)/1024 as total_multi_pages_mb
from sys.dm_os_memory_nodes
where memory_node_id != 64
go
--
-- Total memory used by SQL Server through SQLOS memory nodes
-- including DAC node
-- What takes up the rest of the space?
select (SUM(virtual_address_space_committed_kb)+
SUM(locked_page_allocations_kb)+
SUM(multi_pages_kb))/1024 as total_sql_memusage_mb
from sys.dm_os_memory_nodes
go
--
-- Who are the biggest cache stores?
select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024
as cache_size_mb
from sys.dm_os_memory_cache_counters
where type like 'CACHESTORE%'
group by name, type
order by cache_size_mb desc
go
--
-- Who are the biggest user stores?
select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024
as cache_size_mb
from sys.dm_os_memory_cache_counters
where type like 'USERSTORE%'
group by name, type
order by cache_size_mb desc
go
--
-- Who are the biggest object stores?
select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024
as cache_size_mb
from sys.dm_os_memory_clerks
where type like 'OBJECTSTORE%'
group by name, type
order by cache_size_mb desc
go

--Which object is really consuming from clerk
select * from sys.dm_os_memory_clerks a
,sys.dm_os_memory_objects b
where a.page_allocator_address = b.page_allocator_address
--group by a.type, b.type
order by a.type, b.type
go

--To get the list of 3rd party DLL loaded inside SQL server memory
select * from sys.dm_os_loaded_modules where company <> 'Microsoft Corporation'
go

--Which database page is in my memory
select db_name(database_id),(cast(count(*) as bigint)*8192)/1024/1024 as "size in mb" from sys.dm_os_buffer_descriptors
group by db_name(database_id)

Other SQL Server memory blogs:

https://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/

https://mssqlwiki.com/2012/05/18/sql-server-performance-degraded-in-32-bit-sql-server-after-i-adding-additional-ram/

https://mssqlwiki.com/2012/06/27/a-significant-part-of-sql-server-process-memory-has-been-paged-out/

Other performance blogs:

https://mssqlwiki.com/sqlwiki/sql-performance/io-bottlenecks/

https://mssqlwiki.com/sqlwiki/sql-server-agent/sql-agent-maxworkerthreads-and-agent-subsystem/

https://mssqlwiki.com/sqlwiki/sql-performance/async_network_io-or-network_io/

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

We receive SQL Server error 802 when buffer pool of SQL Server instance is full and cannot grow further. Here, i will discuss about this error 802 insufficient memory available in the buffer pool in this article along with its possible solution to fix it. Below is the error text you might get on your SQL Server system.

SQLServer Error: 802, There is insufficient memory available in the buffer pool. [SQLSTATE 42000]

The main reason behind getting SQL Server error 802 is your SQL Server instance is under memory pressure and there is not enough memory available to allocate further to buffer pool or buffer pool is restricted to not use memory beyond a certain limit. This might be because of various reasons as i have stated in below section. One of the very easy and straight forward approach to fix this error is to increase more memory to SQL Server system and modify max server memory in SQL Server accordingly.

Analysis & Solution

I would suggest to go deeper in this issue before taking any decision to allocate more memory to the system where SQL Server is running. There could be multiple temporary reasons as well which can cause SQL Server to generate below error log:

SQL Server Error: 802, There is insufficient memory available in the buffer pool. [SQLSTATE 42000]

Let’s discuss this issue further and ensure to not forget below points before taking any decisions.

Please comment us about your issue experience and let me know how you have fixed your issue.

Related Articles:

• How to Fix SQL Server Error 701: There is insufficient system memory to run this query
• Understanding Hybrid Buffer Pool in SQL Server
• SQL Server Interview Questions & Answers