sys.dm_db_xtp_object_stats (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2014)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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.

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

sys.dm_db_xtp_object_stats can help you 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).

||
|-|
|Applies to: SQL Server ( SQL Server 2014 through current version).|

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.

See Also

Memory-Optimized Table Dynamic Management Views (Transact-SQL)