SQL Server 2014 In-Memory OLTP Dynamic Management Views

By now you have probably heard that when Microsoft rolls out project Hekaton with SQL Server 2014 they will be calling it In-Memory OLTP.  This new feature allows you to place SQL Server tables in memory.  As you start considering moving disk based tables to memory based tables it will be important to understand how this affects memory and other resources on your SQL Server instance.  To help you better understand the inner workings of the In-Memory OLTP tables and the In-Memory OLTP engine, Microsoft has also released a number of new In-Memory OLTP specific Dynamic Management Views (DMV’s).  In this article I will provide you a quick primer of all these new DMV’s that can help you better understand and manage your In-Memory OLTP tables and your Instance that support In-Memory OLTP tables.

sys.dm_db_xtp_checkpoint_stats

This DMV will return information about In-Memory OLTP checkpoint operations in the current database.   By using this DMV you can you explore the log statistics since SQL Server started up for your databases that contain In-Memory OLTP tables.  With this DMV you can determine stats related to log blocks written, wait statistics, LSN information, and more.    If this DMV is run against a database that doesn’t contain any In-Memory OLTP tables then it will return no data.   For more information about this DMV click here.

sys.dm_db_xtp_checkpoint_files

This DMV shows information about In-Memory checkpoint files.  With this DMV you can determine information about the DATA and DELTA files associated with your In-Memory tables. Using this DMV you can find out information that can be used to determine things such as:

  • Estimating the storage space allocated to your In-Memory tables
  • Determining the number of inserted and deleted rows
  • Determining the % of used space that your In-Memory tables are occupying

For more information about this DMV click here.

sys.dm_db_xtp_gc_cycle_stats

This DMV shows ring buffer output of garbage collection cycles for your In-Memory OLTP database.  You can use this DMV to determine how often the garbage collection process runs against your In-Memory OLTP database. When run against a database that does not support In-Memory OLTP object it returns an empty set.  For more information about this DMV click here.

sys.dm_db_xtp_hash_index_stats

The sys.dm_db_xtp_hash_index_stats DMV allows you to review the statistics associated with HASH indexes that you created on your In-Memory OLTP tables.   By using this DMV you can determine how many buckets are associated with your HASH indexes, how many empty buckets a HASH index has as well as the maximum number of rows chained together in a single bucket, and the average number of rows chained together.   You can use this DMV to determine how well you have set the BUCKET_COUNT option when you created your HASH index.  For more information about this DMV click here.

sys.dm_db_xtp_index_stats

This DMV shows statistics related to In-Memory OLTP objects.  With this DMV you can find out things like:

  • The number of index scans against an In-Memory OLTP index
  • The number of total rows returned
  • The number of rows accessed

Just like other DMV’s, the statistics shown by this DMV are since the index was created or the last time SQL Server was started. For more information about this DMV click here.

sys.dm_db_xtp_memory_consumers

This DMV reports the amount of memory allocated and used based on memory_consumer_id’s.  With this view you can identify the amount of allocated verses used memory for a given database object. For more information about this DMV click here.   

sys.dm_db_xtp_merge_requests

By using this DMV you can track merge requests.  Merge operations are performed in order to manage the checkpoint file pairs (Data and Delta files).  With this DMV you can see the status of the merge requests, as well as identify the source and target files of the merge operations.  For more information about this DMV click here.

sys.dm_db_xtp_table_memory_stats

This DMV will show you the amount of memory used by your In-Memory OLTP tables.    This DMV exposes the memory information for both user and system tables.   By using this DMV you can see the amount of space allocated, and used for both row data and index data.

Here is a sample query that will return the amount of space allocated and used for In-Memory OLTP objects in a database.

SELECT OBJECT_NAME(object_id) ObjectName,
       Object_Id,
       SUM( memory_allocated_for_indexes_kb + memory_allocated_for_table_kb) AS
                    memoryallocated_object_in_kb, 
          SUM( memory_used_by_indexes_kb + memory_used_by_table_kb) AS
                    memoryused_object_in_kb 
FROM sys.dm_db_xtp_table_memory_stats
GROUP by object_id;

Note when you run this DMV if the ObjectName is NULL and the object_id has a negative value then those rows of output represents a system object.  For more information about this DMV click here.

sys.dm_db_xtp_transactions

The sys.dm_db_xtp_transactions DMV shows the active transactions that are being processed by the In-Memory OLTP database engine.  With this DMV you can determine which sessions are processing transactions against In-Memory OLTP tables and what the state of these transactions is.  For more information about this DMV click here.

sys.dm_xtp_gc_queue_stats

This DMV returns information about each garbage collection queue.  By using this DMV you can find out the number of garbage collection work items that have been en-queued and de-queued since your SQL Server instance has started.  Additionally you can find out how many work items are currently in the garbage collection queue and the maximum number of items that have ever been queued up. For more information about this DMV click here.

sys.dm_xtp_gc_stats

By using the sys.dm_xtp_gc_stats DMV you can view statistics related the current behavior of the garbage collection process.  Here is a partial list of some of statistics you can return:

  • The number of rows examined by the garbage collection subsystem since your instance of SQL Server has started.
  • The number of rows examined by garbage collection that were the first row in the hash bucket.
  • The number of rows examined by garbage collection that were the first row in the hash bucket that have been removed.

For more information about this DMV click here.

sys.dm_xtp_system_memory_consumers

This DMV reports system level memory consumers for In-Memory OLTP.  Using this DMV you can determine things like, the type of memory consumer (lookaside, varheap, pgpool, or aggregated) and the number of bytes allocated and used by a consumer. For more information about this DMV click here.

sys.dm_xtp_transaction_stats

This DMV provides rolled up statistics since a SQL Server instance has started for transactions that have processed against In-Memory OLTP tables.     Here is a partial list of the statistics you can return with this DMV:

  • Total count of transactions that have run against the In-Memory OLTP engine
  • Number of read only transactions
  • Number of aborted transactions
  • Number of transaction requiring log IO

For more information about this DMV click here.

Using DMV’s to Understand In-Memory OTLP Engine

It is good to see that Microsoft has not only provided the  great new feature In-Memory OLTP, but they have also provided a lot of DMV that you can use to help you understand stand what is going on in the In-Memory OLTP Engine.  As you start playing with In-Memory OLTP tables you might want to check-out these different DMV’s to see how your In-Memory OLTP tables and engine are doing.  As more people start using the In-Memory OLTP feature and Microsoft moves forward with the rollout of SQL Server 2014 there is bound to start being some best practices advice around how to tune your In-Memory tables and the In-Memory OLTP engine using these DMV.

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles