Monday, August 13, 2012

MSSQL Error 701 There Is Insufficient System Memory

Two days back I had encountered MSSQL Error: 701 There is insufficient system memory, I've never come across this problem before in SQL Server 2008 R2, but long back I had faced similar kind of problem for SQL Server 2005, after installing hotfix it got resolved. I am not exactly sure is there any hotfix is available for SQL Server 2008 R2 or not?

To resolve 701 issue on SQL Server 2008 R2 I have followed below steps and it worked for me:

1. Increase SQL Server Maximum Memory through SQL Server Properties.
 
2. It is actually a bug in the SQL Server. It is not releasing the memory which it holds when it is necessary.
DBCC FreeProcCache -- Clear entire proc cache
DBCC DropCleanBuffers -- Clear entire data cache
DBCC DROPCLEANBUFFERS
DBCC FREESESSIONCACHE

3. If above steps not resolved, check min memory per query, Default value is 1024 KB, it is not recommended to decrease the value but still you can try.
Resolving 701 There is insufficient system memory to run this query. SQL Server reports 701: "There is insufficient memory to run this. Memory Manager Errors. There is insufficient system memory to run this query. Error 701. MSSQLSERVER 701. SQL Server 2005 Error: 701. Severity: 17. State: 193. Memory problem. insufficient system memory. Memory Manager Errors. There is insufficient system memory in resource pool 'internal' to run. SQLServer Error: 802. 701. 733. SQL Error 701 Insufficient Memory. Slow performance and out of memory issues. in sql server 2008. in sql server 2012. in sql server 2000. in sql server 2005.

2 comments:

Can u please confirm detail about installing hot-fix for this issue..

This is how I resolved it.

I have a 32 bit SQL 2005 SP3 installed on Windows Server 2003 Enterprise SP2. I have been getting an error that stated:
There is insufficient system memory to run this query.
Error: 701, Severity: 17, State: 193.

This article helped me understand the structure of the buffer pool when AWE is enabled.
http://blogs.msdn.com/b/psssql/archive/2012/12/11/how-it-works-sql-server-32-bit-pae-awe-on-sql-2005-2008-and-2008-r2-not-using-as-much-ram-as-expected.aspx

The basis of the problem was a memory allocation issue. There are basically three memory areas that SQL Server establishes at start up for a 32 bit system with AWE enabled. They are:
VAS Reserved User memory
Non-reserved user memory
AWE memory

For any 32 bit application on a server that does not have the /3Gb switch on in the boot.ini file, the OS grants 2GB of User memory. This memory, which is directly addressable to the application, consists of the VAS Reserved and non-reserved memory. All memory outside of this range is addressable through additional pointers to AWE memory and can only be used for the buffer pool. The problem that my server had was allocating additional pages for the buffer pool. At first I was puzzled because the server has 64GB of memory and I set the max server memory to 56GB, but when I examined it closer I found that the buffer pool works with AWE memory like the OS uses virtual page files.

32 Bit SQL Servers that are 2005 and above have a visible buffer pool that is within the non-reserved user memory and will swap the data contained in the AWE memory as needed for processing. You can view the size of the visible buffer pool using DBCC memorystatus. The server was having problems because the visible buffer pool was too small for the server workload at the time that the memory error occurred.

If you review the CSS article above, you will find the calculations of how much memory is required to map pointers to the pages in AWE memory. It requires 8MB for each Gb of memory on the server. By default, SQLserver will map all of the physical memory on the server at startup. So my server was establishing 512MB of pointers within the 2GB of user memory, even though I had the max server memory set to 56GB.

The resolution was to use the -T836 startup flag and set the max server memory to 48GB. This told the server to only establish pointers to 48GB of AWE memory,hold them in the user process space and only use 384MB for pointers, leaving more memory for the size of the visible buffer pool. Be sure to test this startup trace flag prior to implementing it in production.

My new challenge is to locate objects in the non-reserved memory where I can either reduce the size or eliminate so I can increase the max server memory again.

Post a Comment