In-Memory OLTP Internals - Pro SQL Server Internals - page 669

Finally, in-memory OLTP is integrated with the database backup and restore functions. It supports piecemeal
restore. However, the in-memory OLTP filegroup should be backed up and restored together with the PRIMARY
filegroup. In most cases, it is not a problem because in-memory OLTP usually contains system-critical data that
should be online for the system to be functional during piecemeal restore. However, you should analyze how this
requirement affects your backup and disaster recovery strategies.

Memory Usage Considerations
It is obvious that in-memory OLTP uses server memory. No further data modifications are possible when memory
cannot be allocated. Moreover, if SQL Server does not have enough memory for in-memory OLTP data at database
startup, the database would not come online. Be sure to remember this when you need to restore a database backup
with in-memory OLTP data on another server that has less memory available, or when you have secondary nodes in a
high-availability solution less powerful than primary ones.
In-memory OLTP memory usage can affect performance of other SQL Server components. For example, SQL
Server would have less memory available for the buffer pool and, therefore, it degrades the performance of queries
against on-disk tables due to the greater amount of physical I/O involved. In-memory OLTP can consume a maximum
of 80 percent of SQL Server memory. However, you can reduce this number by using the Resource Governor.

■ Coverage of resource Governor is outside of the scope of this topic. you can read more about it at:
http://technet.microsoft.com/en-us/library/bb933866.aspx . More information about using resource Governor
with in-memory OLtP is available at: http://msdn.microsoft.com/en-us/library/dn465872.aspx .

Note

In a case of excessive memory usage, you should analyze what objects are consuming the most memory in
in-memory OLTP. You can use the sys.dm_db_xtp_table_memory_stats view to detect these tables. Listing 32-12
shows a query that analyzes memory usage on a per-table basis. Figure 32-28 illustrates the output of the query.

Listing 32-12. Detecting memory usage of memory-optimized tables
select object_name(object_id) as [Object Name], *
from sys.dm_db_xtp_table_memory_stats

Figure 32-28. Memory usage information

■ SQL Server Management Studio includes a “Memory Usage by Memory Optimized Objects” standard report
that provides similar information.

Note

After you detect the memory-consuming tables, you should analyze why they are using memory. If most of the
memory is used by the data, you could consider changing the data layout and partitioning the data by moving part of
it to on-disk tables. Excessive index usage often happens due to over-estimation of bucket counts in hash indexes. You
can use the sys.dm_db_xtp_hash_index_stats view to analyze hash index statistics and possibly recreate a table if
needed. Also think about future data growth during your analysis.