System.OutOfMemoryException for Management Studio


I have seen multiple posts on the web trying to decipher the OutOfMemoryException thrown by Management Studio (SSMS) while executing a user query. Contrary to popular beliefs, this is not a SQL Database Engine Out of Memory condition. This is a client side OOM condition thrown by the .NET runtime while trying to receive the output of the query from the Database Engine. More often than not, the culprit is a large number of rows returned by the query submitted by the user.

A lot of times the error can be circumvented by using the TEXT mode output of SSMS. The error is a .NET Out of Memory exception pointing to the Management Studio running out of available physical memory. The GRID output requires a .NET GridView control to be created where as the Text mode output uses a TextBox to store the output returned by the database engine. The amount of memory consumed by the GRID is higher than a TextBox.

It is always advisable to store the output of a query returns a large number of rows into a file (CTRL+SHIFT+F) or use SQLCMD to generate the output into a CSV/TXT file. This would help optimize the memory usage on the box that is executing the query and also prevent re-execution of the query due to client box out-of-memory conditions. IMHO I cannot fathom the need to output a million rows in the GRID view because it is not possible to parse the output unless you put that into a flat file! Hope this sheds some light on this common misconception.

Once such issue is mentioned below:

Reference: OOM error when we access Schema changes report from SSMS – Microsoft

Advertisements

Non Bpool region on 64bit SQL instances may be larger than you think


One of my older posts talked about SQL Server Memory Basics. The infamous MemToLeave region was shown with a default value of 384MB. This post was penned a long time back and the SQL landscape has changed since then. Since the post was mainly based on showing the memory architecture for SQL Servers in a 32-bit environment, the calculations shown were based on the 2GB user address space limitation for x86 architecture.

With the adoption of 64-bit architecture, the 2GB user mode address space (3GB if you use the /3GB switch) went out of the window. One of the lesser known facts that I have seen multiple times while addressing memory related concerns for non-BPool memory consumption on MSDN is that the non-BPool region is now not just 384MB.

Before I go onto explain this, I would like to point out that the Max Server Memory setting for SQL Server (irrespective of architecture) is concerned with the BPool region. This doesn’t limit the amount of memory that can be used by components which request for contiguous allocations above 8KB in size. The non-BPool region is now limited not by your virtual address space in 64-bit environments but by your physical memory (RAM) on the server. Let me explain this with an example.

If you have a SQL Server instance on a 64-bit box with Max Server Memory set to 24GB and the total physical memory on the server is 32GB, then your SQL instance can now theoretically use the remaining 8GB for contiguous memory allocations requested above 8KB in size. This is where I keep advocating the understanding of the environment before setting Max Server Memory for the SQL instance. Leaving 2 or 3GB for your Operating System and the non-BPool memory is just not going to cut it if you have components active within SQL Server which require huge amounts of contiguous memory. I have already listed out the non-BPool memory consumers in a previous post which outlines the most common consumers. The reason this happens is that on 64-bit environments you are no longer constrained by the 2 or 3GB user address space limitation because your address space runs into a few TBs. So, the limiting factor here is your physical memory (RAM). Once you have set Max Server Memory to finite value, the SQL instance is told that the Buffer Pool cannot grow beyond this value but this doesn’t limit non-BPool allocations. As long as you have available physical memory on the server, SQL can continue using that for contiguous allocation requests above 8KB in size. This makes it highly important for you to evaluate which components in your SQL environment can make such allocation request and set the Max Server Memory in such a manner that you do not starve either of the two memory regions in the SQL Server or any of the other applications running on the server or the Operating System. Unless you do this, you could end up troubleshooting SQL Memory Pressure very frequently on the box especially issues like paging or general server unresponsiveness/sluggishness before you hit the nail on the head!

To summarize, two points that you need to keep in mind for 64-bit SQL environments:

1. Max Server Memory setting only limits Buffer Pool

2. Non-BPool memory region for SQL is effectively now: Total Physical Memory – (Max Server Memory + Physical Memory used by OS and other applications)

How to find who is using/eating up the Virtual Address space on your SQL Server


Well, this is often a tricky situation, where you are running into Virtual Address Fragmentation and getting OOM (out-of-memory) errors in your SQL Server. Most often, people do not understand the difference between physical memory pressure vs. virtual memory pressure. Adding more RAM is definitely not a solution here!         Here are some sample error messages you might have seen in your SQL Errorlog which will indicate if this is physical memory or virtual memory issue,

 SQL 2000
WARNING: Failed to reserve contiguous memory of Size= 65536.
SQL Server could not spawn process_loginread thread.      

SQL 2005 and above
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 122880            

 Error: 701, Severity: 17, State: 123.
There is insufficient system memory to run this query.     

As the errors above indicate, the problem is in reserving a fixed size in the virtual address space of SQL Server. Note: the size indicated above in in bytes. e.g. 65536/1024 = 64 KB

Typical symptoms you would notice in these situations are :-

1) Database or Log Backups might start failing
2) You are unable to make a new connections to SQL.
3) Certain jobs which require memory from non-BPool region will fail.  Many a time, these problems go away automatically without any action taken. This indicates an intermittent problem where at a certain point in time, there was virtual memory pressure which resulted in above messages being printed to the SQL Errorlog.  Dealing with VAS fragmentation or running out of VAS on SQL server 2000 was rather painful and required setting up additional debugging techniques to get down to the bottom of the issue. You might have used some of the following tools :-

1) VMSTAT
2) Debugging Tools for Windows (to capture a manual dump of sqlservr.exe)
3) T2551 to generate a filtered dump in SQL Server when running into a OOM condition.
4) TLIST.exe to identify modules loaded in SQL Server.  Luckily, starting with SQL 2005 there is a in-memory DMV which tracks the virtual address space (VAS) of your SQL Server process.

Here are some queries which will help you find out how much virtual address is available on sqlservr.exe which is FREE and how much is total available (Free+InUse)
 

1. Will tell you the size of the biggest contiguous block in VAS

 

SELECT convert(varchar,getdate(),120) as [Timestamp], max(region_size_in_bytes)/1024 [Total max contiguous block size in KB]  from sys.dm_os_virtual_address_dump where region_state = 0x00010000 --- MEM_FREE 
2. Will also tell us size of largest contiguous block plus the region marked as MEM_RESERVE (this is your non-BPool area reserved during SQL Startup, sometimes referred to as MTL – MemToLeave)

 

 

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)^0)
    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 SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail Mem, KB] ,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB] 
FROM VASummary 
WHERE Free <> 0


 3. Now, below query will identify the memory reserved by non-BPool components in SQL Server

select SUM(virtual_memory_reserved_kb)/1024 as virtual_memory_reserved_mb from sys.dm_os_memory_clerks where type not like '%bufferpool%' 
4. To identify if any of the space used is by SQL Server procedure cache itself, you can use this query:

 

 

SELECT SUM(PAGESUSED)*8/1024 'MB of MemToLeave memory consumed by procedures' FROM MASTER.DBO.SYSCACHEOBJECTS WHERE PAGESUSED >1

DBCC MEMORYSTATUS also has good information on whether the usage from the non-BPool region is coming from SQL or non-SQL components. If its SQL 2000, check the value of “OS Committed” and it is SQL 2005/2008 look at the value of “MultiPage Allocator” for each memory clerk. Just to re-state my assumption in case you are not sure:
1 SQL Server Page = 8 KB –> SinglePage Allocator
> 1 Page or > 8KB –> MultiPage Allocator    Apart from these you need to pay special attention to the following components are all of the below do not use memory from the Buffer Pool region but make direct VirtualAlloc() calls to reserve memory and then commit them:

 1) Any OLE/COM components loaded in SQL Server
2) Extended Stored Procedures (use sys.dm_os_loaded_modules to identify the module loaded in sqlserver process space).
3) SQL Mail components
4) Any prepared documents using sp_xml_preparedocument
5) Linked Server Providers
6) Large Plans stored in Procedure Cache
7) Very frequent Backups also may cause MTL area depletion. (Please investigate using the parameters MAXTRANSFERSIZE and BUFFERCOUNT, if this is the case).
8) SQL CLR (recommended to be used on 64-bit SQL Servers)

Above list is certainly not exhaustive, but is more enough to get started in looking at the right areas. While we are on this topic, it is important to understand the difference between a Reserve and a Commit. These are windows concepts are remain the same for SQL Server as well, after all its VirtualAlloc() underneath the covers.

MEM_COMMIT – Region in VAS this is backed by RAM/paging file
MEM_RESERVE – Region in VAS with no actual physical storage either in RAM or in the paging file.  The problems we talked about so far occur when a call to VirtualAlloc() with MEM_RESERVE is made and that “reservation” fails resulting in the errors printed to errorlog. Most times, the call to reserve is subsequently followed by a COMMIT, but its not mandatory. I can reserve now and commit later on using the base address of the reservation. SQL Server is smart enough and during starting reserves a certain region of the address space referred to as MTL or Non-BPool region. It is here the crunch is and it is here the issue needs to be investigated/fixed.

A quick workaround for above issues is to add the startup parameter –gXXX. (Sample usage- -g512)
XXX- is the amount in MBytes to reserve on startup.I would advise against doing this as this is a workaround where you are increasing the MTL region rather than find out who/what is consuming it. Slava’s blog is a good read also on this topic.