SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server - Error 701 - There is insufficient system memory

20 November,2012 by Jack Vamvas

According to BOL the Error 701 is “SQL Server has failed to allocate sufficient memory to run the query. This can be caused by a variety of reasons including operating system settings, physical memory availability, or memory limits on the current workload. In most cases, the transaction that failed is not the cause of this error.”

 A very common error I see for Bpool memory pressure is the FAIL_PAGE_ALLOCATION 1. It’s for one page i.e. 8 KB.

To find the cause requires to search for a memoryclerk or cachestore which consumes the highest memory.

First step is to identify which memoryclerk or  cachestore is consuming the highest memory.When you inspect the Error Logs , there is normally the DBCC memorystatus output.

 An example of a cachestore with high memory usage at the time of the incident is :

 

CACHESTORE_SQLCP (Total) 
	VM Reserved = 0 KB 
	VM Committed = 0 KB 
	AWE Allocated = 0 KB 
	SM Reserved = 0 KB 
	SM Committed = 0 KB
	SinglePage Allocator = 1473672 KB
	MultiPage Allocator = 400 KB

 

The CACHESTORE_SQLCP stores cache plans for batches or T-SQL statements not in stored procedures, triggers or functions. In practise, these type of statements are used irregulary , although you’ll need to analyse. In this scenario , the CACHESTORE_SQLCP  was using 1.4 GB of physical memory.In the context of the server memory usage this was high.

This query is useful in presenting an overview of the plan cache size

SELECT TOP(5) * FROM 
sys.dm_Exec_cached_plans 
WHERE cacheobjtype = 'Compiled Plan' AND objtype = 'Adhoc' 
AND usecounts = 1 
AND size_in_bytes < 5242880 ORDER BY size_in_bytes DESC

 

There are many different scenarios for Error 701 - There is insufficient system memory, requiring different approaches.

Read More

SQL Server Performance ,memory pressure and memory usage ...

SQL Server – Measuring SQL memory usage - SQL Server DBA

SQL Server – Memory leak detection - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer