System Views, Stored Procedures, DMVs and Wait Types for In-Memory OLTP
This topic provides brief descriptions and links to the many database objects that support In-Memory OLTP.
System Views
| System View | Description | In-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 Function | Description | In-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 Procedure | Description |
|---|---|
| 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. |
Dynamic Management Views (DMVs)
There are several DMVs for memory-optimized tables.
For details, see Memory-Optimized Table Dynamic Management Views (Transact-SQL).
Wait Types
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.
See Also
In-Memory OLTP (In-Memory Optimization)
Transact-SQL Support for In-Memory OLTP