System Views, Stored Procedures, DMVs and Wait Types for In-Memory OLTP

SQL Server 2014
 

This topic provides brief descriptions and links to the many database objects that support In-Memory OLTP.

System Views

System ViewDescriptionIn-Memory OLTP feature
sys.data_spaces (Transact-SQL)Check if a filegroup contains memory-optimized data.The following columns display additional values: type and type_desc.
sys.indexes (Transact-SQL)Check if an index is on a memory-optimized table.The following columns display additional values: type and type_desc.
sys.parameters (Transact-SQL)Check a parameter is not-nullable (for more efficient execution of a natively-compiled stored procedure).is_nullable column.
sys.all_sql_modules (Transact-SQL)Check if a stored procedure is natively compiled.uses_native_compilation column.
sys.sql_modules (Transact-SQL)Check if a stored procedure is natively compiled.uses_native_compilation column.
sys.table_types (Transact-SQL)Check if a table is memory-optimized.is_memory_optimized column.
sys.tables (Transact-SQL)Check if a table is memory-optimized, and check a table’s durability setting.durability, durability_desc, and is_memory_optimized columns.
sys.hash_indexes (Transact-SQL)Show the hash indexes of a memory-optimized table.In-memory OLTP specific.

Metadata Functions

Metadata FunctionDescriptionIn-Memory OLTP feature
OBJECTPROPERTYEX (Transact-SQL)Check if database objects are memory-optimized.ExecIsWithNativeCompilation and TableIsMemoryOptimized properties.

The IsSchemaBound property supports the Procedure object type (returns 0 for procedures instead of NULL).
SERVERPROPERTY (Transact-SQL)Check if a server supports In-Memory OLTP.IsXTPSupported property.

System Stored Procedures

Stored ProcedureDescription
sys.sp_xtp_bind_db_resource_pool (Transact-SQL)Bind an In-Memory OLTP database to a resource pool.
sys.sp_xtp_checkpoint_force_garbage_collection (Transact-SQL)Initiate garbage collection on an In-Memory OLTP database.
sys.sp_xtp_control_proc_exec_stats (Transact-SQL)Enable statistics collection for natively compiled stored procedures.
sys.sp_xtp_control_query_exec_stats (Transact-SQL)Enable per query statistics collection for natively compiled stored procedures.
sys.sp_xtp_merge_checkpoint_files (Transact-SQL)Merge data and delta files.
sys.sp_xtp_unbind_db_resource_pool (Transact-SQL)Remove the binding between a database and a resource pool.

There are several DMVs for memory-optimized tables.

For details, see Memory-Optimized Table Dynamic Management Views (Transact-SQL).

There are several wait types that support In-Memory OLTP.

For details, see wait types that are prefixed with WAIT_XTP, and XTPPROC in the sys.dm_os_wait_stats (Transact-SQL) topic.

In-Memory OLTP (In-Memory Optimization)
Transact-SQL Support for In-Memory OLTP

Show: