Memory-optimized tables must fully reside in memory and can\u2019t be paged out. Unlike disk-based tables where insufficient memory can slowdown an application, the impact to \u00a0memory-optimized tables upon encountering out-of-memory can be severe, causing\u00a0 DML (i.e. delete, insert or update) operations to fail. While this adds a new dimension to managing memory, the application failure due to resource errors is not something new. For example, applications using disk-based tables can fail with resource errors such as running out of transaction log or TempDB or out of storage. It is the responsibility of DBAs\/Administrators to make sure resources are provisioned and managed appropriately to avoid such failures. SQL Server provides a rich set of monitoring tools, including DMVs, PerfMon and XEvents to help administrators identify problems earlier so that a corrective action can be taken. Similarly, for memory-optimized tables, SQL Server provides a rich set of monitoring capabilities and configuration options so that you can manage your database\/instance well and keep your application running smoothly.\u00a0 The remainder of this blog walks thru each of the challenges and details how it can be addressed.<\/p>\n
This is the first question that you need consider when migrating an existing table(s) to memory-optimized table(s) or when you are considering a new application using memory-optimized tables. When migrating a disk-based table, you know, for example using sp_spaceused as described in http:\/\/technet.microsoft.com\/en-us\/library\/ms188776.aspx<\/a> , \u00a0its current size so it is just a simple mathematical calculation to find the corresponding size for memory-optimized tables. \u00a0The key differences to be aware of are that memory-optimized tables cannot compressed like disk-based tables with ROW and PAGE compression, so, the memory-optimized table will likely be bigger. However, unlike indexes for disk-based tables, the indexes on memory tables are much smaller. For example, the index key is not stored with hash indexes and all indexes are, by definition, covered indexes. Please refer to http:\/\/msdn.microsoft.com\/en-us\/library\/dn247639(v=sql.120).aspx<\/a> for details. A more challenging task is to estimate the data growth.\u00a0 While you can make a reasonable guess, the best way is the continuous to monitor the table size and the memory consumed by memory-optimized table (s) in your database and instance.\u00a0 The same monitoring approach holds for new applications that are created with in-memory OLTP in mind.<\/p>\n The in-memory OLTP engine is integrated with SQL Server. Memory allocations to memory-optimized tables are managed by SQL Server Memory Manager and the allocated memory is tracked using familiar constructs and tools such as memory clerks and DMVs. The following DMV shows XTP memory clerks. The first row shows the memory allocated by system threads. The second row with name DB_ID_5 represents the consumers in the database objects and the third row with memory node-id 64 represents memory allocated to DAC (Dedicated Admin Connection).<\/p>\n — Show the memory allocated to in-mempory OLTP objects<\/em><\/em><\/p>\n select<\/em> type, name, memory_node_id, pages_kb\/1024 as pages_MB <\/em><\/p>\n from<\/em> sys.dm_os_memory_clerks where type like ‘%xtp%’<\/em><\/p>\n type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 memory_node_id pages_MB<\/em><\/p>\n ——————– ———- ————– ——————–<\/em><\/p>\n MEMORYCLERK_XTP\u00a0\u00a0\u00a0\u00a0\u00a0 Default\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18<\/em><\/p>\n MEMORYCLERK_XTP\u00a0\u00a0\u00a0\u00a0\u00a0 DB_ID_5\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1358<\/em><\/p>\n MEMORYCLERK_XTP\u00a0\u00a0\u00a0\u00a0\u00a0 Default\u00a0\u00a0\u00a0 64\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0<\/em><\/p>\n Also, there are new DMVs that can be used to monitor the memory consumed by the in-memory OLTP engine and memory-optimized tables. Please refer to http:\/\/msdn.microsoft.com\/en-us\/library\/dn133203(v=sql.120).aspx<\/a> for details.<\/p>\n Like any other memory consumer, the in-memory OLTP engine responds to memory-pressure, but to a limited degree. For example, the memory consumed by data and indexes can\u2019t be released even under memory pressure. This is different than disk-based tables where an external memory pressure may cause the buffer pool to shrink which simply means there will be fewer data\/index pages in memory. For this reason, it is all the more important to provision the memory for memory-optimized tables appropriately, otherwise in-memory OLTP engine \u00a0can starve other memory consumers including the memory needed by SQL Server for its operations which can ultimately leads to slow or unresponsive application. To address this, SQL provides a configuration option to limit the memory consumed by memory-optimized tables.<\/p>\n Starting with SQL Server 2014, you can bind a database to a Resource Pool. This binding is only relevant when the database has one or more memory-optimized table. The memory available in the resource pool controls the total memory available to memory-optimized tables in the database.<\/p>\n For example, create a resource pool, mem_optpool as follows<\/p>\n CREATE<\/em> RESOURCE POOL mem_optpool WITH (MAX_MEMORY_PERCENT = 40);<\/em><\/p>\n Now map the database, mydatabase<\/em>, to this resource pool by executing the following command. With this command, you are specifying that the total memory taken by memory-optimized tables and indexes cannot exceed the limit in the resource pool. So for this case, the other 60% memory is available to other consumers.<\/p>\n EXEC<\/em> sp_xtp_bind_db_resource_pool ‘mydatabase’, ‘mem_optpool’<\/em><\/p>\n When configuring memory for memory-optimized tables, the capacity planning should be done based on MIN_MEMORY_PERCENT, not on MAX_MEMORY_PERCENT. This provides more predictable memory availability for memory-optimized tables as pools that have the min_memory_percent option set can cause memory pressure notifications against other pools to ensure the minimum percentage is honored.. To ensure that memory is available for the In-Memory OLTP database and help avoid OOM (Out of Memory) conditions, the values for MAX_MEMORY_PERCENT and MIN_MEMORY_PERCENT should be the same. SQL Server target memory is dynamic relative to the OS and setting a minimum memory would be recommended only if the server is not dedicated. For details, please refer to http:\/\/msdn.microsoft.com\/en-us\/library\/dn465873(v=sql.120).aspx<\/a>.<\/p>\n The rows for memory-optimized tables are stored in-memory and are linked through Hash and non-clustered indexes as described http:\/\/msdn.microsoft.com\/en-us\/library\/dn133190(v=sql.120).aspx<\/a>. Concurrent access to memory-optimized table uses optimistic concurrency control\u00a0 based on row versions.\u00a0\u00a0 Over time, the existing rows may get updated (update operation generates a row version(s)) and deleted but these rows can\u2019t immediately be removed as there may be concurrent transactions that need these rows versions. These older row versions are garbage collected (GC\u2019d) asynchronously when it is determined, based on the active transactions, that they are no longer needed. There is a GC system thread that shares the row version cleanup (i.e. GC) with user transaction activity to ensure that SQL Server is able to keep up with the GC. \u00a0\u00a0When you configure the memory for your workload, you must account for additional memory needed for stale row versions. You can roughly estimate the memory needed for stale row versions using the following steps:<\/p>\nHow does SQL Server manage memory for memory-optimized tables?<\/h2>\n
How can I limit memory consumed by memory-optimized tables?<\/h2>\n
How does SQL Server reclaim memory taken by deleted\/updated rows<\/h2>\n