Sql-server – SQL Server Internal Memory Error

memorymemory-optimized-tablessql serversql-server-2016

I am using SQL Server 2016 Standard Edition that shows the following errors on DBCC CHECKDB and also on updating statistics. 

 Version: Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 (X64) 
 Jun 28 2018 17:24:28 Copyright (c) Microsoft Corporation Standard Edition 
 (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

MAX Memory set at 215GB from 244GB x1e.2xlarge AWS EC2
There are around 260 databases.
Locked Pages in Memory is on.
Page File size is 8GB.

Memory optimized tables that uses less than 10GB of memory.

Failed to reserve pages: FAIL_PAGE_RESERVATION

Error: 8645, Severity: 17, State: 1. A timeout occurred while waiting
for memory resources to execute the query in resource pool 'internal'
(1). Rerun the query.

Error: 701, Severity: 17, State: 135. There is insufficient system
memory in resource pool 'internal' to run this query.

After multiple errors like these, the server shutdown itself.

Page life expectancy is 1297050, no memory grants pending, doesn't appear to have any memory pressure.

Any ideas on this issue?

Best Answer

Just so we have 100% clarity, memory-optimized tables:

  1. don't use the buffer pool, so PLE is not relevant
  2. are ignored by CHECKB
  3. memory consumed by memory-optimized objects cannot be swapped out for any reason, so page file is not relevant

The limit of 128GB for Standard Edition applies only to the buffer pool. Comments by others that say SQL Server cannot use more than 128GB for Standard are incorrect.

For Standard Edition, there is a cap on the total size of all memory-optmized data/indexes and clustered columnstore. The combined total cannot exceed 32GB per database.

Related Question