| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.dm_db_xtp_object_stats (Transact-SQL) |
sys.dm_db_xtp_object_stats reports the number rows affected by operations on each of the In-Memory OLTP objects since the last database restart. |
rwestMSFT |
randolphwest |
02/27/2023 |
sql |
system-objects |
reference |
|
|
|
>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDEsql-asdb-asdbmi]
Reports the number rows affected by operations on each of the [!INCLUDEinmemory] 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 [[!INCLUDEinmemory] (In-Memory Optimization)](../in-memory-oltp/overview-and-usage-scenarios.md).
| 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. |
Requires VIEW DATABASE STATE permission on the current database.
Requires VIEW DATABASE PERFORMANCE STATE permission on the database.
- Introduction to Memory-Optimized Tables
- Memory-Optimized Table Dynamic Management Views
- [[!INCLUDEinmemory] Overview and Usage Scenarios](../in-memory-oltp/overview-and-usage-scenarios.md)
- Optimize performance by using in-memory technologies in Azure SQL Database
- Optimize performance by using in-memory technologies in Azure SQL Managed Instance