次の方法で共有

You may see “out of user memory quota” message in errorlog when you use In-Memory OLTP feature

  • [アーティクル]

Recently we got an inquiry from a customer who received the following message in errorlog and wanted to know why.

[INFO] HkDatabaseTryAcquireUserMemory(): Database ID: [7]. Out of user memory quota: requested = 131200; available = 74641; quota = 34359738368; operation = 1.

This is my first time to see this error.  As usual, I relied on source code to find answers.   The message is a result of enforcing memory quota for In-memory OLTP usage.  As documented in “In-Memory OLTP in Standard and Express editions, with SQL Server 2016 SP1”, SQL Server 2016 SP1 started to allow In-Memory OLTP to be used in all editions but enforce memory quotas for editions other than Enterprise edition.  The above message is simply telling you that you have reached the quota and what ever operation you did was denied.

In addition to the message in errorlog, end user (application) should have received the following message per blog “In-Memory OLTP in Standard and Express editions, with SQL Server 2016 SP1”.

Msg 41823, Level 16, State 171, Line 6 Could not perform the operation because the database has reached its quota for in-memory tables. See 'https://go.microsoft.com/fwlink/?LinkID=623028' for more information.

 

Let’s take a closer look at the message printed in errorlog:

[INFO] HkDatabaseTryAcquireUserMemory(): Database ID: [7]. Out of user memory quota: requested = 131200; available = 74641; quota = 34359738368; operation = 1.

  1. 131200 was number of bytes requested.
  2. 74641 was number of bytes available to use.
  3. 34359738368 was the quota in bytes (which is 32 GB for standard edition)
  4. operation = 1 means the memory was requested to create a memory optimized table variable.

In order to help you see what kind of operation, here is a complete list of ‘operation’:

number operation
0 create memory optimized table
1 create memory optimized table variable
2 insert
3 update

 

Knowing what each number means will give you more information to investigate.

Additionally, if you are using memory optimized table in the context of table valued parameters repeatedly, you may encounter this error even you use them in a separate batch and memory gets released in SQL Server standard edition.   This has been fixed in a hotfix. See https://support.microsoft.com/en-us/help/4025208/fix-memory-leak-occurs-when-you-use-memory-optimized-tables-in-microso。

 

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

Comments