- Overview of In-Memory OLTP
- In-Memory Optimization Requirements
- Limitations of In-Memory OLTP
- Using In-Memory OLTP
- Using Memory-Optimized Tables
- Logging, Checkpoint, and Recovery for In-Memory OLTP
- Managing Memory for In-Memory OLTP
- Backup and Recovery of Memory-Optimized Databases
- Migrating to In-Memory OLTP
- Dynamic Management Views for In-Memory OLTP
- The Buffer Pool Extension
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Dynamic Management Views for In-Memory OLTP
The introduction of In-Memory OLTP in SQL Server 2014 brings with it a number of new DMVs that provide information about the In-Memory OLTP engine and memory-optimized tables and indexes. Each of these new DMVs and functions that contain ”xtp” (an acronym of eXtreme Transaction Processing) in its name refers to the In-Memory OLTP Engine.
There are two types of In-Memory OLTP Dynamic Management Views. The Database specific DMVs provide information and statistics for the current database and start with ”sys.dm_db_xtp_”. Instance specific DMVs return information and statistics concerning the entire In-Memory OLTP Engine of the instance and start with ”sys.dm_xtp_”.
A number of the new DMVs have been presented previously in various sections in this chapter. The following is a complete list of the DMVs with a brief description of them:
- sys.dm_db_xtp_checkpoint_stats—Shows checkpoint statistics of the current database.
- sys.dm_db_xtp_checkpoint_files—This DMV shows information about checkpoint files, like the type of file (DATA or DELTA files), its size, and relative path.
- sys.dm_db_xtp_gc_cycle_stats—Shows garbage collection cycles for the current database.
- sys.dm_db_xtp_hash_index_stats—This DMV is very useful to deal with hash indexes. You can determine if a hash index is low on buckets or has many duplicate keys.
- sys.dm_db_xtp_index_stats—This DMV contains statistics about hash and range indexes collected since the last database restart. It is the memory-optimized tables equivalent to sys.dm_db_index_usage_stats.
- sys.dm_db_xtp_memory_consumers—This DMV reports stats for memory consumers of the current database. The view returns a row for each memory consumer that the database engine uses.
- sys.dm_db_xtp_merge_requests—Use this DMV to view status of data and delta files’ merge operations, both automatic and manual.
- sys.dm_db_xtp_table_memory_stats—Shows allocated and used memory of user and system tables.
- sys.dm_db_xtp_transactions—Displays information about current transactions in the In-Memory OLTP database engine.
- sys.dm_xtp_gc_stats—Gives information about the garbage-collection (GC) process on memory-optimized tables.
- sys.dm_xtp_gc_queue_stats—Outputs information about each GC worker queue on the server, and various statistics about each.
- sys.dm_xtp_system_memory_consumers—Reports information on system level memory consumers for In-Memory OLTP.
- sys.dm_xtp_transaction_stats—Reports statistics about transactions that have run since the server started.
In addition to the above listed DMVs, there are also a few useful undocumented DMVs related to In-Memory OLTP:
- sys.dm_db_xtp_nonclustered_index_stats—Displays statistics on range indexes in memory-optimized tables.
- sys.dm_db_xtp_object_stats—Reports row insert, update, and delete attempts in memory-optimized tables.
- sys.dm_xtp_threads—Shows information about In-Memory OLTP threads like Base Address and thread type.
- sys.dm_xtp_transaction_recent_rows—As its name says, returns information of recent rows within transactions.
- sys.fn_dblog_xtp—Displays transaction log information similar to sys.fn_dblog but with additional In-Memory OLTP related columns.
- sys.fn_dump_dblog_xtp—Same as above, but also works with backup devices just like sys.fn_dump_dblog.