sys.dm_xtp_transaction_stats – Day 66 – One DMV a Day

Hello Geeks and welcome to the Day 66 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday we have seen how to check the transactions on In-Memory tables using  sys.dm_db_xtp_transactions. Today I will cover sys.dm_xtp_transaction_stats. This DMV gives you an idea about how loaded is your server in terms of transactions.

Sys.dm_xtp_transaction_stats is an accumulated result of the stats from the server start up. So you will see all the aggregated information for following columns.

total_counts – Total transactions which executed in In-Memory OLTP engine.

read_only_count – Count of read-only transactions.

total_aborts – Number of transactions which aborted.

validation_failure – Number of times a transaction aborts due to validation failure.

dependencies_failed – Number of times a transaction has aborted as the dependent transaction aborted.

savepoints_created – Number of savepoints created. Every atomic block creates a savepoint.

savepoint_rollbacks – Number of times a rollback occurred on previous savepoint.

log_bytes_written – Number of log bytes written in to In-Memory log records.

log_IO_count – Number of transactions that need log IO. This is only considered with durable tables.

Let us see the output of sys.dm_xtp_transaction_stats.

SELECT total_count,
	read_only_count,
	total_aborts,
	validation_failures,
	dependencies_failed,
	savepoint_create,
	savepoint_rollbacks,
	log_bytes_written,
	log_IO_count 
FROM sys.dm_xtp_transaction_stats

sys.dm_xtp_transaction_stats

Now the interesting part is Microsoft marks most of other columns as internal use only. But below are few columns which I think are not so internal and which I can make sense from the name. I am covering few which I thought are useful. Rest of them are useful too on case to case basis.

phantom_rows_touched – Number of phantom records that were touched in all transactions.

scans_started – Number of scans started by all transactions.

rows_returned – number of rows returned by all transactions.

rows_touched – number of rows accessed but may not be returned.

row_insert_attempts – Number of attempts to insert rows.

row_update_attempts – Number of attempts to perform an update operation.

row_delete_attempts – Number of delete attempts.

write_conflict – Conflict between transactions while writing a row which leads to aborts.

unique_constraint_violations – Number of unique constraint violations from all transactions.

Lot more internal use only to be explored. I need to build labs to see the use cases of all these columns. 🙂 Will do it someday.

Tomorrow I will be covering another In-Memory optimized tables related DMV. So stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *