{"id":4274,"date":"2015-01-22T14:47:00","date_gmt":"2015-01-22T13:47:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/"},"modified":"2015-01-22T14:47:00","modified_gmt":"2015-01-22T13:47:00","slug":"memory-management-oom-issues-with-in-memory-oltp","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/","title":{"rendered":"Memory management, OOM issues with SQL Server 2014 In-memory OLTP"},"content":{"rendered":"

\"\"<\/p>\n

Last week I gave a workshop about SQL Server 2014 and the new features. The first day we worked on new In-memory OLTP and different topics such as the new internal storage, the new transaction processing behavior or the new checkpointing process. During this day, one of the attendees asked me about the memory management with In-Memory OLTP feature. It was a very interesting question but unfortunately I didn\u2019t have the time to discuss about it with him, so I decided to publish something concerning this topic. This subject may be extensive and time consuming, so I will try to give only a good overview to understand correctly how memory management works against memory-optimized objects and how important the monitoring aspect is in this particular context.<\/p>\n

First of all, keep in mind that memory-optimized tables are memory-oriented feature. It means that memory-optimized structures (indexes and data rows) will reside exclusively in memory. This is by design and this point is very important. Let me explain why later in this blog post.<\/p>\n

For the moment, let\u2019s focus on memory aspects of this new feature. In-memory OLTP is not different from other memory consumers on SQL Server. Indeed, In-memory OLTP objects have their own memory clerk MEMORYCLERK_XTP. Let\u2019s have a look at the sys.dm_os_memory_clerks DMV to show information concerning In-Memory OLTP allocated memory.<\/p>\n

select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [type], <\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name, <\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 memory_node_id, <\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 page_size_in_bytes,<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 pages_kb\/1024 as size_MB<\/span><\/span><\/span><\/div>\n
from sys.dm_os_memory_clerks where type like ‘%xtp%’;<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
go<\/span><\/div>\n

\"blog_27_1_memory_clerk\"<\/span><\/p>\n

In my case we may notice that the database dbi_hk (DB_ID = 24) contains memory-optimized objects with a dedicated memory clerk on it. Others xtp memory clerks are dedicated for system threads (first line) and DAC (last line) but let\u2019s focus on my user database memory clerk which has 2336MB of page memory allocated.<\/p>\n

On my lab environment, I have only one memory-optimized table named bigTransactionHistory_xtp inside the dbi_hk database. Let\u2019s have a look at the new DMV sys.dm_db_xtp_table_memory_stats<\/strong> to show memory information for this table:<\/p>\n

SELECT<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 object_name(object_id) AS table_name,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 memory_allocated_for_indexes_kb \/ 1024 as mem_alloc_index_mb,<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 memory_allocated_for_table_kb \/ 1024 as mem_alloc_table_mb,<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 memory_used_by_indexes_kb \/ 1024 as mem_used_index_mb,<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 memory_used_by_table_kb \/ 1024 as mem_used_table_mb,<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (memory_allocated_for_table_kb + memory_allocated_for_indexes_kb) \/ 1024 as mem_alloc_total_mb,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (memory_used_by_table_kb + memory_used_by_indexes_kb) \/1024 as mem_used_total_mb<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
FROM sys.dm_db_xtp_table_memory_stats<\/span><\/span><\/span><\/span><\/div>\n
where object_id = object_id(‘bigTransactionHistory_xtp’);<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
go<\/span><\/div>\n

\"blog_27_2_xtp_table_memory_stats\"<\/p>\n

We may expect to retrieve the same amount of memory page allocated here and in the dedicated memory clerk of the dbi_hk database. This is approximatively the case. The difference we found concerns probably memory allocated for system internal structures. We may have a look at the concerned DMV sys.dm_db_xtp_memory_consumers but I will focus on it in a next blog post.<\/p>\n

At this point we know where to find information concerning the memory consumption for memory-optimized objects but I still have one question in mind: how does SQL Server memory manager deal with memory concurrent activities between memory-optimized tables and their disk-based table counterparts? Like any other memory consumer, the in-memory OLTP engine responds to memory-pressure, but to a limited degree because memory consumed by data and indexes can\u2019t be released even under memory pressure.<\/p>\n

To deal correctly with In-Memory OLTP engine and others consumers we have to turn on the resource governor (RG) side. Indeed, by default all databases are mapped to the default resource pool regardless the RG is enabled. In the same way, workloads issued from both disk-based tables and memory-optimized tables will run concurrently on the default resource pool if any special configuration is performed. In such case, RG will use an internal threshold for In-Memory OLTP to avoid conflicts over pool usage. The threshold is depending on the memory size configured for SQL Server and especially to the target commit memory for the SQL Server instance. You can refer to the Microsoft documentation here<\/a> for more details.<\/p>\n

So, in my case the max memory setting value is configured to 6144MB and the target committed memory is as follows:<\/p>\n

select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 committed_target_kb \/ 1024 as committed_target_mb<\/span><\/span><\/span><\/div>\n
from sys.dm_os_sys_info;<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n

 <\/p>\n

\"blog_27_3_target_commit_memory\"<\/p>\n

According the Microsoft documentation (cf. link above) the percent available for in-memory tables will be 70% or 0.7 * 4898 = 3429MB. I may retrieve this information by using the DMV related on the RG. You can find an original version of this script on MSSQLTIPs.com<\/a> website.<\/p>\n

;with cte<\/span><\/span><\/span><\/div>\n
as (<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 select\u00a0\u00a0 <\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RP.pool_id ,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RP.Name ,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RP.min_memory_percent ,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RP.max_memory_percent ,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cast(RP.max_memory_kb \/ 1024. as numeric(12, 2)) AS max_memory_mb ,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cast(RP.used_memory_kb \/ 1024. as numeric(12, 2)) AS used_memory_mb ,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cast(RP.target_memory_kb \/ 1024. as numeric(12,2)) AS target_memory_mb,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cast(SI.committed_target_kb \/ 1024. as numeric(12, 2)) AS committed_target_mb <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0 from sys.dm_resource_governor_resource_pools RP<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0 cross join sys.dm_os_sys_info SI<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
)<\/span><\/div>\n
select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.pool_id ,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.Name ,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.min_memory_percent ,<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.max_memory_percent ,<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.max_memory_mb ,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.used_memory_mb ,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.target_memory_mb , <\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.committed_target_mb,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST(c.committed_target_mb * case when c.committed_target_mb then 0.7<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 when c.committed_target_mb < 16384 then 0.75<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 when c.committed_target_mb < 32768 then 0.8<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 when c.committed_target_mb then 0.85<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 when c.committed_target_mb > 98304 then 0.9<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 end * c.max_memory_percent \/ 100 as numeric(12,2)) as [Max_for_InMemory_Objects_mb],<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST(c.committed_target_mb * case when c.committed_target_mb then 0.7<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 when c.committed_target_mb < 16384 then 0.75<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 when c.committed_target_mb < 32768 then 0.8<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 when c.committed_target_mb then 0.85<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 when c.committed_target_mb > 98304 then 0.9<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 end * c.max_memory_percent \/ 100 as numeric(12,2)) – c.used_memory_mb as Free_for_InMemory_Objects_mb<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
FROM cte c;<\/span><\/span><\/span><\/div>\n
go<\/span><\/div>\n

\"blog_27_4_RG_memory_available_for_xtp_tables\"<\/p>\n

Ok I retrieve (approximatively) this value by looking at the Max_for_InMemory_Objects_mb<\/strong> column on the default pool line record. Notice that we have already 2008MB used in the default resource pool.<\/p>\n

At this point the In-memory OLTP and the disk-based OLTP run concurrently on the same resource pool and of course, this is not a recommended situation. Indeed, we may be in a situation where In-Memory OLTP consumes all the available memory from this pool. In such situation, SQL Server will be forced to flush data pages from disk-based tables and you know the performance impact of this process.<\/p>\n

Go ahead and let\u2019s create an issue you can faced with In-Memory OLTP and misconfigured environments. First we decrease the max memory setting value to 4096MB and then we load another bunch of data into bigTransactionHistory_xtp table to consume an important part of the available memory dedicated to memory-optimized objects in the default resource pool. Finally let\u2019s have again a look at the RG memory configuration by using the previous script. We have now a good picture of changes applied after our reconfiguration:<\/p>\n

\"blog_27_5_RG_memory_available_for_xtp_tables\"<\/p>\n

As expected, several values have changed for target memory, memory available for memory-optimized tables and memory used memory into the default resource pool. The new available memory value for the resource pool is now 1605MB (3891MB \u2013 2286MB). I let you think about a bad situation where your memory-optimized table will consume all available memory inside the default resource pool in order of magnitude \u2026 the consequences are obvious (even if they depend on the context): probably a lot of memory pressures between the buffer pool consumer and In-Memory OLTP consumer and in the worst case a potential OOM issue like as follows:<\/p>\n

\"blog_27_6_RG_default_pool_oom_condition\"<\/p>\n

After loading data into the bigTransactionHistory_xtp we can notice we have consumed all available memory for In-memory objects into the default resource pool. However as said earlier, RG guarantees a certain amount of memory for disk-based tables.<\/p>\n

\"blog_27_6_RG_memory_available_oom_condition\"<\/p>\n

Ok now let\u2019s simulate a crash recovery scenario by restarting the SQL Server instance. In my case the SQL Server engine service didn\u2019t restart correctly\u2026 ouch… What\u2019s going on? Of course my first though was to take a look directly on the error log of my SQL Server instance. The first error message I encountered was as follows:<\/p>\n

\"blog_27_7_xtp_start_instance_error_fail_page_allocation\"<\/p>\n

Ok… it seems there is an issue during the dbi_hk recovery database process. In fact during the recovery process one step consists in building the index structure and link the data rows to this structure. But you can see that this step fails with an OOM (Out Of Memory) issue.<\/p>\n

\"blog_27_8_xtp_start_instance_oom_1\"<\/p>\n

\"blog_27_8_xtp_start_instance_oom_2\"<\/span><\/p>\n

In this second part, we have interesting information concerning our OOM issue. First of all in the \u201cprocess \/ system counts\u201d section we may notice that SQL Server had to deal with internal memory pressures (process physical memory low = 1) so we can exclude external memory pressure. Then, in the \u201cmemory manager\u201d section we have two additional sections Last OOM Factor<\/strong> and Page Alloc Potential<\/strong>. The former confirms an OOM (out of memory) issue into the Memory manager. The latter shows a negative value that indicates that the buffer pool does not have any free memory so our assumption that it was an internal memory pressure is correct. As a reminder Page Alloc Potential<\/strong> is similar to Stolen Potential<\/strong> in previous versions of SQL Server.<\/p>\n

Let\u2019s continue and point out the memory clerks which are responsible for the memory pressure. By investigating down into the log file, I found two relevant memory clerks with a lot of pages allocated as shown above:<\/p>\n

\"blog_27_9_xtp_clerk_status\"<\/p>\n

\"blog_27_9_xtp_clerk_status_2\"<\/span><\/p>\n

As expected, the first memory clerk concerns In-memory OLTP (XTP as Extreme Transaction Processing) and the second is related on the log pool manager that is heavily used during recovery processing. The both memory clerks, at the time of the OOM issue, have a total size of 3.7GB. This does not leave much room for the caches left in the default resource pool. Finally the end of the error log contains the following error messages that confirm that SQL Server is missing memory for its default resource pool.<\/p>\n

\"blog_27_9_xtp_clerk_status_3\"<\/p>\n

According to the Microsoft documentation<\/a> that\u2019s the resolution of OOM issues with In-Memory table\u2019s scenarios, the number of solutions are very limited. In my case, I started the SQL Server engine with \u2013f parameter to load minimal configuration and then I increased the amount of memory dedicated to In-memory OLTP by increasing the max server memory option in the server side. This fix will avoid to face the same issue on the next restart of my SQL Server engine service.<\/p>\n

Is it possible to fix definitely this OOM condition? The response is yes and we have to configure a resource pool with memory limitations and bind it with our memory-optimized database. This is another story and I let you check the Microsoft document<\/a>! My intention in this blog post is only to create awareness of the importance of a good memory management with new In-memory OLTP feature.<\/p>\n

Happy configuration!<\/p>\n

By David Barbarin<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Last week I gave a workshop about SQL Server 2014 and the new features. The first day we worked on new In-memory OLTP and different topics such as the new internal storage, the new transaction processing behavior or the new checkpointing process. During this day, one of the attendees asked me about the memory management […]<\/p>\n","protected":false},"author":26,"featured_media":3590,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[431,222,417,51,52],"type_dbi":[],"class_list":["post-4274","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-application-integration-middleware","tag-hekaton","tag-in-memory","tag-in-memory-oltp","tag-sql-server","tag-sql-server-2014"],"acf":[],"yoast_head":"\nMemory management, OOM issues with SQL Server 2014 In-memory OLTP - dbi Blog<\/title>\n<meta name=\"description\" content=\"Last week I gave a workshop about SQL Server 2014 and the new features. The first day we worked on new In-memory OLTP and different topics such as the new internal storage, the new transaction processing behavior or the new checkpointing process.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Memory management, OOM issues with SQL Server 2014 In-memory OLTP\" \/>\n<meta property=\"og:description\" content=\"Last week I gave a workshop about SQL Server 2014 and the new features. The first day we worked on new In-memory OLTP and different topics such as the new internal storage, the new transaction processing behavior or the new checkpointing process.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-01-22T13:47:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"245\" \/>\n\t<meta property=\"og:image:height\" content=\"149\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/memory-management-oom-issues-with-in-memory-oltp\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/memory-management-oom-issues-with-in-memory-oltp\\\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"Memory management, OOM issues with SQL Server 2014 In-memory OLTP\",\"datePublished\":\"2015-01-22T13:47:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/memory-management-oom-issues-with-in-memory-oltp\\\/\"},\"wordCount\":1853,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/memory-management-oom-issues-with-in-memory-oltp\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/2e1ax_default_entry_sqlserver2014.jpg\",\"keywords\":[\"hekaton\",\"In-memory\",\"In-Memory OLTP\",\"SQL Server\",\"SQL Server 2014\"],\"articleSection\":[\"Application integration & Middleware\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/memory-management-oom-issues-with-in-memory-oltp\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/memory-management-oom-issues-with-in-memory-oltp\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/memory-management-oom-issues-with-in-memory-oltp\\\/\",\"name\":\"Memory management, OOM issues with SQL Server 2014 In-memory OLTP - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/memory-management-oom-issues-with-in-memory-oltp\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/memory-management-oom-issues-with-in-memory-oltp\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/2e1ax_default_entry_sqlserver2014.jpg\",\"datePublished\":\"2015-01-22T13:47:00+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"description\":\"Last week I gave a workshop about SQL Server 2014 and the new features. The first day we worked on new In-memory OLTP and different topics such as the new internal storage, the new transaction processing behavior or the new checkpointing process.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/memory-management-oom-issues-with-in-memory-oltp\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/memory-management-oom-issues-with-in-memory-oltp\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/memory-management-oom-issues-with-in-memory-oltp\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/2e1ax_default_entry_sqlserver2014.jpg\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/2e1ax_default_entry_sqlserver2014.jpg\",\"width\":245,\"height\":149},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/memory-management-oom-issues-with-in-memory-oltp\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Memory management, OOM issues with SQL Server 2014 In-memory OLTP\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/microsoft-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Memory management, OOM issues with SQL Server 2014 In-memory OLTP - dbi Blog","description":"Last week I gave a workshop about SQL Server 2014 and the new features. The first day we worked on new In-memory OLTP and different topics such as the new internal storage, the new transaction processing behavior or the new checkpointing process.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/","og_locale":"en_US","og_type":"article","og_title":"Memory management, OOM issues with SQL Server 2014 In-memory OLTP","og_description":"Last week I gave a workshop about SQL Server 2014 and the new features. The first day we worked on new In-memory OLTP and different topics such as the new internal storage, the new transaction processing behavior or the new checkpointing process.","og_url":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/","og_site_name":"dbi Blog","article_published_time":"2015-01-22T13:47:00+00:00","og_image":[{"width":245,"height":149,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"Memory management, OOM issues with SQL Server 2014 In-memory OLTP","datePublished":"2015-01-22T13:47:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/"},"wordCount":1853,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg","keywords":["hekaton","In-memory","In-Memory OLTP","SQL Server","SQL Server 2014"],"articleSection":["Application integration & Middleware"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/","url":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/","name":"Memory management, OOM issues with SQL Server 2014 In-memory OLTP - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg","datePublished":"2015-01-22T13:47:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"description":"Last week I gave a workshop about SQL Server 2014 and the new features. The first day we worked on new In-memory OLTP and different topics such as the new internal storage, the new transaction processing behavior or the new checkpointing process.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg","width":245,"height":149},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/memory-management-oom-issues-with-in-memory-oltp\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Memory management, OOM issues with SQL Server 2014 In-memory OLTP"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4274","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4274"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4274\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/3590"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4274"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4274"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4274"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4274"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}