Export (0) Print
Expand All

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 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.


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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft