2

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?

  • Is it a DBCC CHECKDBon a specific database? How big is the database? Same for the size of the table where update statistics runs on. Also, is the update statistics on a mem optimized table when the error happens? – Randi Vertongen Apr 18 at 7:59
  • What is your full build version? – Randi Vertongen Apr 18 at 8:05
  • @lemniscate, Can you update your error message ID. – Md Haidar Ali Khan Apr 18 at 8:43
  • @RandiVertongen 2016SP2CU4; checkdb on MSDB size was around 5GB; update statistics were on normal databases – lemniscate Apr 18 at 8:53
  • 2
    You said you have set max memory to 215 GB but SQL Server 2016 standard has maximum limit of 128GB for database engine. Are you aware about this ? Could this not be the reason for what you are seeing ? – Shanky Apr 18 at 11:46
0

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.

  • Yep, all combined usage is less than 10GB so the issue probably isn't related to memory-optimized tables – lemniscate Apr 19 at 7:09
  • Speaking on behalf of the others, I'm responding to the comment in the question: MAX Memory set at 215GB. This should not set higher than 128MB. You can, but you'll run into memory errors like are being displayed. – John Eisbrener Apr 23 at 13:39
0

You state:

MAX Memory set at 215GB

As @Shanky stated in the comments, set max server memory (MB) to 128 GB via sp_configure as that's the limit of usable memory for SQL Server Standard Edition.

I've run into a similar issue with SQL Server Express edition when I had it set higher than it's usable limit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.