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.