sys.dm_db_xtp_object_stats (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Reports the number rows affected by operations on each of the In-Memory OLTP objects since the last database restart. Statistics are updated when the operation executes, regardless of whether the transaction commits or was rolled back.

The sys.dm_db_xtp_object_stats system dynamic management view can help you identify which memory-optimized tables are changing the most. You may decide to remove unused or rarely used indexes on the table, as each index affects performance. If there are hash indexes, you should periodically re-evaluate the bucket-count. For more information, see Determining the Correct Bucket Count for Hash Indexes.

Use sys.dm_db_xtp_object_stats to identify which memory-optimized tables incur write-write conflicts, which can affect the performance of your application. For example, if you have transaction retry logic, the same statement may need to be executed more than once. Also, you can use this information to identify the tables (and therefore business logic) that require write-write error handling.

The view contains a row for each memory optimized table in the database.

For more information, see In-Memory OLTP (In-Memory Optimization).

Column name Data type Description
object_id bigint The ID of the object.
row_insert_attempts bigint The number of rows inserted into the table since the last database restart by both committed and aborted transactions.
row_update_attempts bigint The number of rows updated in the table since the last database restart by both committed and aborted transactions.
row_delete_attempts bigint The number of rows deleted from the table since the last database restart by both committed and aborted transactions.
write_conflicts bigint The number of write conflicts that occurred since the last database restart.
unique_constraint_violations bigint The number of unique constraint violations that have occurred since the last database restart.
object_address varbinary(8) Internal use only.

Permissions

Requires VIEW DATABASE STATE permission on the current database.

Permissions for SQL Server 2022 and later

Requires VIEW DATABASE PERFORMANCE STATE permission on the database.